Option Explicit On

Imports System.Text


Public Class ReportCRM

    Public Enum ReportType
        Commission = 1
        InvoiceList = 2
        InvoiceListV2 = 3
        CashUp = 4
        EventSummaryConsultant = 5
        PriceList = 6
        CaseStages = 7
        PipelinePreSale = 8
        SalesStatusSummary = 9
        VisasExpiring = 10
        OpenCasesNoInvoice = 11
        EnteredBySummary = 12
        MigrationCaseStages = 13
        LeadsPerConsultant = 14
        WebLeadsPerDay = 15
        WebSalesPerDay = 16
        LeadsByNationalityTop10 = 17
        ConversionRateConsultant = 18
        ConversionRateProduct = 19
        SalesByNationality = 20
        LeadsByNationality = 21
        CaseWorkerGranted = 22
        WorkInProgress = 23
        LeadsAssigned = 24
        ProformaCompany = 1000
        ProformaIndividual = 1001
        AccountStatement = 1002
        PrivateReceipt = 100
        CorporateReceipt = 101
        InvoiceReport = 1003
        NormalProformaCompany = 1004
        NormalProformaIndividual = 1005
        NoEventCases = 1006
        EventPrint = 1007
        WriteOffList = 1008

    End Enum
    Public Enum EventView
        Company = 1
        Person = 2
        CaseId = 3
        Consultant = 4
        All = 5
        Combined = 6
    End Enum
    Public Enum ConsultantRoles
        SoldBy = 1
        CaseWorker = 2
    End Enum

    Public Enum ProductSelection
        All = 1
        By_Department = 2
        Retired_Products = 3
        By_Product_Type = 4
    End Enum

    Public Enum AccountTypes
        All = 1
        SLA = 2
        ExcludeSLA = 3
    End Enum

    Public Enum ClientTypes
        All = 1
        PrivateOnly = 2
        CorporateOnly = 3
    End Enum

    Public Structure ReportCriteria
        'Used in SQL
        '***********************
        Public Consultant_All As Boolean
        Public Consultant_Id As Integer
        Public Branch_id As Integer
        Public Branch_Name As String
        Public Consultant_DeptId As Integer
        Public Consultant_Role As ConsultantRoles
        Public StartDate As String
        Public EndDate As String
        Public Product_Selection As ProductSelection
        Public Product_Selection_Id As Object 'Varies depending on whether department, type
        Public Account_Types As AccountTypes
        Public Client_Type As ClientTypes
        '***********************
        'Used for formula fields in report
        '************************
        Public Period As String
        Public Consultant_Name As String
        Public Product_Name As String
        '************************
        Public CurrentReport As ReportType
    End Structure
    Public Shared Function ManipulateCollec(ByVal caseids As DataTable) As String
        Dim strCase As String = String.Empty
        Dim i As Integer
        For i = 0 To caseids.Rows.Count - 1
            If strCase = String.Empty Then
                strCase = caseids.Rows(i).Item(0).ToString
            Else
                strCase = strCase & "," & caseids.Rows(i).Item(0).ToString
            End If
        Next
        Return strCase
    End Function

    Public Shared Function GetReportData(ByVal ReportId As ReportType, Optional ByVal id As Long = 0) As DataTable
        Dim db As New CRM.DataLayer.DataAccess

        Dim strSQL As StringBuilder
        Try

            Dim SQL As String = String.Empty

            Dim myData As DataTable = Nothing
            Dim dtTemp As DataTable
            Dim dtData1 As DataTable
            Dim dr() As DataRow = Nothing
            Dim i As Integer

            Select Case ReportId

                Case ReportType.Commission
                    SQL = SQL_Commission()

                Case ReportType.InvoiceList
                    SQL = SQL_InvoiceList()
                    myData = db.GetSQLDataTable(SQL)
                    dtTemp = New DataTable
                    dtTemp.Columns.Add("Invoice_ID")
                    If Not myData Is Nothing Then
                        If myData.Rows.Count > 0 Then
                            For i = 0 To myData.Rows.Count - 1
                                dtTemp.Rows.Add(myData.Rows(i).Item("Invoice_ID"))
                            Next
                        End If
                    End If
                    Dim strInv As String = ManipulateCollec(dtTemp)
                    strSQL = New StringBuilder
                    With strSQL
                        .Append("SELECT     TOP 100 PERCENT Charge_InvoiceID FROM  dbo.A_Charges WHERE  (Charge_InvoiceID IN (")
                        .Append(strInv)
                        .Append(")) AND (Charge_Type LIKE 'Writeoff')")
                    End With

                    dtData1 = db.GetSQLDataTable(strSQL.ToString)
                    Dim strId As String = ManipulateCollec(dtData1)
                    If Not strId Is Nothing Then
                        dr = myData.Select("Invoice_id in (" & strId & ")")
                    End If
                    'If Not dtData1 Is Nothing Then
                    '    If dtData1.Rows.Count > 0 Then
                    '        For i = 0 To dtData1.Rows.Count - 1
                    '            dr = myData.Select("Invoice_ID = " & dtData1.Rows(i).Item(0).ToString)

                    '        Next
                    '    End If
                    'End If
                    If Not dr Is Nothing Then

                        For i = 0 To dr.Length - 1
                            myData.Rows.Remove(dr(i))
                        Next
                    End If
                Case ReportType.WriteOffList
                    SQL = SQL_InvoiceList()
                    myData = db.GetSQLDataTable(SQL)
                    dtTemp = New DataTable
                    dtTemp.Columns.Add("Invoice_ID")
                    If Not myData Is Nothing Then
                        If myData.Rows.Count > 0 Then
                            For i = 0 To myData.Rows.Count - 1
                                dtTemp.Rows.Add(myData.Rows(i).Item("Invoice_ID"))
                            Next
                        End If
                    End If
                    Dim strInv As String = ManipulateCollec(dtTemp)
                    strSQL = New StringBuilder
                    With strSQL
                        .Append("SELECT     TOP 100 PERCENT Charge_InvoiceID FROM  dbo.A_Charges WHERE  (Charge_InvoiceID IN (")
                        .Append(strInv)
                        .Append(")) AND (Charge_Type <> 'Writeoff')")
                    End With

                    dtData1 = db.GetSQLDataTable(strSQL.ToString)
                    Dim strId As String = ManipulateCollec(dtData1)
                    If Not strId Is Nothing Then
                        dr = myData.Select("Invoice_id in (" & strId & ")")
                    End If
                    'If Not dtData1 Is Nothing Then
                    '    If dtData1.Rows.Count > 0 Then
                    '        For i = 0 To dtData1.Rows.Count - 1
                    '            dr = myData.Select("Invoice_ID = " & dtData1.Rows(i).Item(0).ToString)

                    '        Next
                    '    End If
                    'End If
                    If Not dr Is Nothing Then
                        For i = 0 To dr.Length - 1
                            myData.Rows.Remove(dr(i))
                        Next
                    End If
                Case ReportType.InvoiceListV2
                    SQL = SQL_InvoiceListV2()

                Case ReportType.CashUp
                    SQL = SQL_CashUp()

                Case ReportType.EventSummaryConsultant
                    SQL = SQL_EventSummaryConsultant()

                Case ReportType.PriceList
                    SQL = SQL_PriceList()

                Case ReportType.CaseStages
                    SQL = SQL_CaseStages()

                Case ReportType.PipelinePreSale
                    SQL = SQL_PipeLinePreSale()

                Case ReportType.SalesStatusSummary
                    SQL = SQL_SalesStatusSummary()

                Case ReportType.VisasExpiring
                    SQL = SQL_VisasExpiring()

                Case ReportType.OpenCasesNoInvoice
                    SQL = SQL_OpenCasesNoInvoice()

                Case ReportType.EnteredBySummary
                    SQL = SQL_EnteredBySummary()

                Case ReportType.MigrationCaseStages
                    SQL = SQL_MigrationCaseStages()

                Case ReportType.LeadsPerConsultant
                    SQL = SQL_LeadsPerConsultant()

                Case ReportType.WebLeadsPerDay
                    SQL = SQL_WebLeadsPerDay(GlobalCRM.ReportCriteria.StartDate, GlobalCRM.ReportCriteria.EndDate)
                Case ReportType.WebSalesPerDay
                    SQL = SQL_WebSalesPerDay(GlobalCRM.ReportCriteria.StartDate, GlobalCRM.ReportCriteria.EndDate)

                Case ReportType.LeadsByNationalityTop10
                    SQL = SQL_LeadsByNationalityTop10()


                Case ReportType.ConversionRateConsultant

                    'Requires extra processing as multiple queries
                    Dim arrSQL() As String = SQL_ConversionByConsultant()

                    myData = GenerateConversionConReport(arrSQL(0), arrSQL(1))

                    'myData.WriteXmlSchema("c:\crap\ConversionCon.xsd")

                    Return myData


                Case ReportType.ConversionRateProduct

                    'Requires extra processing as multiple queries
                    Dim arrSQL() As String = SQL_ConversionByProduct()

                    myData = GenerateConversionProdReport(arrSQL(0), arrSQL(1))


                    Return myData

                Case ReportType.SalesByNationality
                    SQL = SQL_SalesByNationality()

                Case ReportType.LeadsByNationality
                    SQL = SQL_LeadsByNationality()

                Case ReportType.ProformaCompany
                    SQL = SQL_ProformaCompany(GlobalCRM.CurrentCaseID)

                Case ReportType.NormalProformaCompany
                    SQL = SQL_ProformaCompany(GlobalCRM.CurrentCaseID)

                Case ReportType.ProformaIndividual
                    SQL = SQL_ProformaIndividual(GlobalCRM.CurrentCaseID)

                Case ReportType.NormalProformaIndividual
                    SQL = SQL_ProformaIndividual(GlobalCRM.CurrentCaseID)

                Case ReportType.AccountStatement
                    '  SQL = SQL_AccountStatement()

                Case ReportType.CaseWorkerGranted
                    SQL = SQL_CaseWorkerGranted()

                Case ReportType.WorkInProgress
                    SQL = SQL_WorkInProgress()


                Case ReportType.LeadsAssigned
                    SQL = SQL_LeadsAssigned()

                Case ReportType.PrivateReceipt
                    SQL = SQL_PrivateReceipt(GlobalCRM.CurrentPaymentId)

                Case ReportType.CorporateReceipt
                    SQL = SQL_CorporateReceipt(GlobalCRM.CurrentPaymentId)

                Case ReportType.InvoiceReport
                    SQL = SQL_InvoiceReport(GlobalCRM.CurrentCaseID)

                Case ReportType.NoEventCases
                    SQL = SQL_NoEventCases()
                Case ReportType.EventPrint
                    Dim s As Long
                    SQL = SQL_EventPrint(id)
                    'Case ReportType.EventsAllPrint
                    'SQL = SQL_EventPrintAll()
            End Select
            If myData Is Nothing Then
                myData = db.GetSQLDataTable(SQL)
            End If
            ' myData.WriteXmlSchema("c:\crap\CorporateReceipt.xsd")

            Return myData
        Catch ex As Exception
            Return Nothing
        Finally
            strSQL = Nothing
        End Try

    End Function

    Private Shared Function SQL_ConversionByProduct() As String()

        Dim SQL As New StringBuilder
        Dim arrSQL(1) As String

        'Leads
        With SQL
            .Append("SELECT  TOP 100 PERCENT p.Product_Name, co.Country_Name, COUNT(*) AS totalleads, 0 AS totalsales, 0.0 AS convrate, dbo.L_Branch.Branch_Name ")
            .Append(" FROM  dbo.D_Case AS c INNER JOIN")
            .Append("  dbo.L_Products AS p ON c.Case_ProductID = p.Product_ID INNER JOIN ")
            .Append("  dbo.L_ProductTypes AS pt ON p.Product_Type = pt.ProductType_ID INNER JOIN")
            .Append("  dbo.L_Country AS co ON p.Product_DestinationID = co.Country_ID INNER JOIN")
            .Append("  dbo.L_Branch ON pt.ProductType_BranchID = dbo.L_Branch.Branch_ID")
            .Append(" where")
            .Append(ReportCriteria_Dates("c.Case_DTallocated"))
            .Append(" GROUP BY p.Product_Name, co.Country_Name, dbo.L_Branch.Branch_Name ")
            .Append(" order by p.product_name")
            arrSQL(0) = .ToString()
        End With

        SQL = New StringBuilder

        'Sales
        With SQL
            .Append("SELECT     TOP 100 PERCENT p.Product_Name, co.Country_Name, COUNT(*) AS totalsales, 0.0 AS conrate, dbo.L_Branch.Branch_Name ")
            .Append(" FROM         dbo.D_Case AS c INNER JOIN ")
            .Append("   dbo.L_Products AS p ON c.Case_ProductID = p.Product_ID INNER JOIN")
            .Append("   dbo.L_ProductTypes AS pt ON p.Product_Type = pt.ProductType_ID INNER JOIN")
            .Append("   dbo.L_Country AS co ON p.Product_DestinationID = co.Country_ID INNER JOIN")
            .Append("   dbo.L_Branch ON pt.ProductType_BranchID = dbo.L_Branch.Branch_ID")
            '.Append(" dbo.L_Country AS co ON p.Product_DestinationID = co.Country_ID")
            .Append(" where ")
            .Append(ReportCriteria_Dates("c.Case_DTallocated"))
            .Append("  AND (c.Case_DTSold IS NOT NULL) AND (c.Case_StatusID <> 1030)")
            .Append(" GROUP BY p.Product_Name, co.Country_Name, dbo.L_Branch.Branch_Name")
            .Append(" order by p.product_name")
            arrSQL(1) = .ToString
        End With

        Return arrSQL

    End Function

    Private Shared Function SQL_ConversionByConsultant() As String()

        Dim SQL As New StringBuilder
        Dim arrSQL(1) As String

        'Leads

        With SQL
            .Append("select con.con_name,count(*) as totalleads, 0 as totalsales, 0.0 as convrate ")
            .Append("from d_case c, l_products p, l_consultant con ")
            .Append("where ")
            .Append(ReportCriteria_Dates("c.Case_DTAllocated"))
            .Append(" and c.case_productid = p.product_id ")
            .Append("and c.case_consaleid = con.con_id ")
            .Append("group by con.con_name ")
            .Append("order by con.con_name")
            arrSQL(0) = .ToString
        End With

        'Sales

        SQL = New StringBuilder

        With SQL

            .Append("select con.con_name,count(*) as totalsales ")
            .Append("from d_case c, l_products p, l_consultant con ")
            .Append("where ")
            .Append(ReportCriteria_Dates("c.Case_DTAllocated"))
            .Append(" and c.case_dtsold is not null and c.case_statusid <> 1030")
            .Append(" and c.case_productid = p.product_id")
            .Append(" and c.case_consaleid = con.con_id")
            .Append(" group by con.con_name")
            .Append(" order by con.con_name")
            arrSQL(1) = .ToString
        End With

        Return arrSQL

    End Function

    Private Shared Function SQL_LeadsByNationalityTop10() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select top 10 co.country_nationality,count(*) as natcount ")
            .Append("from d_case c, l_products p, l_country co, d_person per,l_producttypes pt ")
            .Append("where ")
            .Append(ReportCriteria_Dates("c.Case_DTentered"))
            .Append(" and c.case_candidateid = per.person_id")
            .Append(" and per.person_natid = co.country_id")
            .Append(" and c.case_productid = p.product_id")
            .Append(" and p.product_type = pt.producttype_id ")
            .Append(" " & ReportCriteria_Product("case_productid"))
            .Append(" group by co.country_nationality")
            .Append(" order by natcount desc")

        End With

        Return SQL.ToString


    End Function

    Private Shared Function SQL_LeadsByNationality() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select co.country_nationality,dep.department_name,cast('01-' + left(datename(month,c.case_dtentered),3) + '-' + datename(year,c.case_dtentered) as datetime) as leaddate,count(*) as totalleads")
            .Append(" from d_case c, l_products p, l_country co, d_person per, l_producttypes pt, l_departments dep")
            .Append(" where")
            .Append(ReportCriteria_Dates("c.Case_DTentered"))
            .Append(" and c.case_productid = p.product_id")
            .Append(" and c.case_candidateid = per.person_id")
            .Append(" and per.person_natid = co.country_id")
            .Append(" and p.product_type = pt.producttype_id")
            '.Append(" " & ReportCriteria_Product("case_productid"))
            .Append(" and pt.producttype_departmentid = dep.department_id")
            .Append(" group by co.country_nationality,dep.department_name,dep.department_name,cast('01-' + left(datename(month,c.case_dtentered),3) + '-' + datename(year,c.case_dtentered) as datetime)")
            .Append(" order by leaddate,co.country_nationality, totalleads desc")
            Return .ToString
        End With

    End Function

    Private Shared Function SQL_SalesByNationality() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select co.country_nationality,dep.department_name,cast('01-' + left(datename(month,c.case_dtsold),3) + '-' + datename(year,c.case_dtsold) as datetime) as solddate,count(*) as totalsales ")
            .Append("from d_case c, l_products p, l_country co, d_person per, l_producttypes pt, l_departments dep ")
            .Append("where ")
            .Append(ReportCriteria_Dates("c.case_dtsold"))
            .Append(" and c.case_statusid <> 1030")
            .Append(" and c.case_productid = p.product_id")
            .Append(" and c.case_candidateid = per.person_id")
            .Append(" and per.person_natid = co.country_id")
            .Append(" and p.product_type = pt.producttype_id")
            .Append(" and pt.producttype_departmentid = dep.department_id")
            .Append(" group by co.country_nationality,dep.department_name,dep.department_name,cast('01-' + left(datename(month,c.case_dtsold),3) + '-' + datename(year,c.case_dtsold) as datetime)")
            .Append(" order by solddate,co.country_nationality, totalsales desc")
            Return .ToString
        End With

    End Function

    Private Shared Function SQL_WebSalesPerDay(ByVal StartDate As String, ByVal EndDate As String)

        Dim SQL As New StringBuilder

        With SQL
            .Append("sp_WebSalesByCountry '" & StartDate & "','" & EndDate & "'")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_WebLeadsPerDay(ByVal StartDate As String, ByVal EndDate As String)

        Dim SQL As New StringBuilder

        With SQL
            .Append("sp_WebLeadsByCountry '" & StartDate & "','" & EndDate & "'")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_CaseWorkerGranted() As String

        Dim SQL As New StringBuilder

        With SQL

            .Append("select con_name,count(*) from d_case, l_consultant where ")
            .Append(ReportCriteria_Dates("case_dtgranted") & " ")
            .Append("and case_concwid = con_id group by con_name")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_MigrationCaseStages() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select c1.case_id as caseid, s.casestage_title as stage, per.person_fname + ' ' + per.person_lname as client,")
            .Append("con1.con_name as salescon,coalesce(con2.con_name,'UNASSIGNED') as caseworker,p.product_name,")
            .Append("c1.case_dtsold,sum(ch.charge_amount) as charged, sum(pay.payment_amount) as received ")
            .Append("from d_case c1, l_products p, l_producttypes pt, l_casestages s, d_person per, l_consultant con1, a_charges ch, a_payments pay,")
            .Append("d_case c2 left join l_consultant con2 on con2.con_id = c2.case_concwid  WHERE c1.case_productid = p.product_id ")
            .Append("and p.product_type = pt.producttype_id and pt.producttype_destinationid <> 71 ")
            .Append("and c1.case_statusid between 100 and 999 and c1.case_statusid = s.casestage_number ")
            .Append("and c1.case_dtentered >= getdate() -365 ")
            .Append("and c1.case_candidateid = per.person_id ")
            .Append("and c1.case_consaleid = con1.con_id and c1.case_id = c2.case_id ")
            .Append("and c1.case_id = ch.charge_caseid ")
            .Append("and c1.case_id = pay.payment_caseid group by c1.case_id, s.casestage_title, per.person_fname + ' ' + per.person_lname,con1.con_name,coalesce(con2.con_name,'UNASSIGNED'),p.product_name, ")
            .Append("c1.case_dtsold order by c1.case_dtsold")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_LeadsAssigned() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("Select c.Case_ID,c.Case_DtEntered,Case_StatusID,cy.Country_Name,")
            .Append("pr.Product_Name,IsNull(con.con_name,'Unassigned') as Sales_Consultant ")
            .Append("From D_Case c Left Outer Join D_Person p On c.Case_CandidateID = p.Person_ID ")
            .Append("Left Outer Join L_Country cy On p.Person_ResidentID = cy.Country_ID ")
            .Append("Inner Join L_Products pr On c.Case_ProductID = pr.Product_ID ")
            .Append("Left Outer Join L_Consultant con On c.Case_ConSaleID = con.Con_ID ")
            .Append("Where ")
            .Append(ReportCriteria_Dates("c.case_dtentered") & " ")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_EnteredBySummary() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT Sum(CASE WHEN D_Case.Case_StatusID between 0 AND 99 THEN 1 ")
            .Append("WHEN  D_Case.Case_StatusID between 100 AND 999 THEN 0 END)  AS lead, ")
            .Append("Sum(CASE WHEN D_Case.Case_StatusID between 0 AND 99 THEN 0 ")
            .Append("WHEN  D_Case.Case_StatusID between 100 AND 999 THEN 1 END)  AS sold,")
            .Append("Sum(CASE WHEN D_Case.Case_StatusID >1000 THEN 1 ")
            .Append("When D_Case.Case_StatusID <1000  then 0 END) AS Cancelled,")
            .Append("L_Consultant_1.con_name AS Sale, D_Case.Case_EnteredBy AS [Added By] ")
            .Append("FROM D_Case LEFT OUTER JOIN ")
            .Append("L_Consultant L_Consultant_1 ON D_Case.Case_ConSaleID = L_Consultant_1.Con_ID LEFT OUTER JOIN ")
            .Append("L_Consultant ON D_Case.Case_ConCWID = L_Consultant.Con_ID ")
            .Append("WHERE NOT (D_Case.Case_CompanyID = 3065) AND ")
            .Append(ReportCriteria_Dates("case_dtentered") & " ")
            .Append("GROUP BY L_Consultant_1.con_name, D_Case.Case_EnteredBy ")
            .Append("ORDER BY L_Consultant_1.con_name")
        End With

        Return SQL.ToString


    End Function
    Private Shared Function SQL_EventPrint(ByVal EventID As Long) As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Event.Event_ID, D_Event.Event_Description, D_Event.Event_Message, D_Event.Event_Enteredby, D_Event.Event_DTEntered, D_Event.Event_LinkID, ")
            .Append(" L_Consultant.con_name, ISNULL(L_OISC.OISC_Description, 'Unregistered') AS OISC, ")
            .Append("  CASE Event_LinkType WHEN 1 THEN 'Case' WHEN 2 THEN 'Person' WHEN 3 THEN 'Company' END AS LinkType ")
            .Append("  FROM D_Event Left Outer Join L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID LEFT OUTER JOIN ")
            .Append(" L_OISC ON L_Consultant.Con_OISC = L_OISC.OISC_ID ")
            .Append("WHERE (D_Event.Event_ID = " & EventID & ")")

            '" WHERE " & ReportCriteria_Dates("invoice_date")
        End With

        Return SQL.ToString


    End Function
    Public Shared Function SQL_EventPrintAll(ByVal EventType As EventView, ByVal LinkID As Long) As DataTable

        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder
        Try
            Dim ActivePersonId As Long ' - this needs change


            Dim LngContactID As Long, LngCandidateID As Long

            Select Case EventType

                Case EventView.Company

                    'Get list of employees in company
                    Dim Employees() As Long = Company.EmployeeList(LinkID)
                    Dim i As Integer
                    Dim EmployeeList As New StringBuilder

                    If IsNothing(Employees) Then
                        Return Nothing
                    End If

                    For i = Employees.GetLowerBound(0) To Employees.GetUpperBound(0)
                        EmployeeList.Append(Employees(i))
                        If i < Employees.GetUpperBound(0) Then
                            EmployeeList.Append(",")
                        End If
                    Next

                    SQL = New StringBuilder

                    With SQL
                        .Append("SELECT D_Event.Event_ID, Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end as EventDT, D_Event.Event_Description, L_Consultant.con_name,")
                        .Append(" CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + convert(varchar(12),Event_DTScheduled,103) WHEN 3 THEN 'Completed on ' + convert(varchar(12),Event_DTCompleted,103) END AS Category, ")
                        .Append(" Case Event_LinkType when 3 then 'Company' ")
                        .Append(" when 2 then D_Person.Person_Fname + ' ' + D_Person.Person_Lname")
                        .Append(" else 'Case' End as LinkFor, Event_Message")

                        'From Clause
                        .Append(" FROM D_Event LEFT OUTER JOIN")
                        .Append(" L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")
                        .Append(" Left Outer Join D_Person on D_Event.Event_LinkID = D_Person.Person_ID")

                        'VB Code Defnition Where Clause (Item ID and Link Type defined above)
                        .Append(" WHERE ((D_Event.Event_LinkType = 3) And (D_Event.Event_LinkID = " & LinkID & "))")
                        If EmployeeList.ToString <> "" Then
                            .Append(" OR ((Event_LinkType = 2) AND (Event_LinkID in (" & EmployeeList.ToString & ")))")
                        End If

                        'Order By
                        .Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc")
                    End With

                    Return db.GetSQLDataTable(SQL.ToString)
                    'DataBinding.BindListView(SQL.ToString, Me.lvwEvents)


                Case EventView.Person
                    'By Person

                    'Select clause
                    SQL = New StringBuilder
                    With SQL
                        .Append("SELECT     TOP 100 PERCENT Event_ID,  CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END AS EventDT, ")
                        .Append("     Event_Description, ISNULL(ConScheduled, ConEntered) AS con_name, CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + CONVERT(varchar(12), Event_DTScheduled, 103) ")

                        'From Clause
                        .Append("    WHEN 3 THEN 'Completed on ' + CONVERT(varchar(12), Event_DTCompleted, 103) END AS Category, 'Person' AS Linkfor, Event_Message")
                        .Append(" FROM   dbo.V_Events WHERE     (Event_LinkType = 2) AND (Event_LinkID = " & LinkID & ")")

                        'VB Code Defnition Where Clause (Item ID and Link Type defined above)
                        .Append(" ORDER BY CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END DESC")

                        'Order By
                        '.Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc")
                        'DataBinding.BindListView(.ToString, Me.lvwEvents)
                    End With

                    'Call GblFunc_Populate_List(StrSQL, "MainDisplay_Event_List", Form_Fm_MainDisplay.List_Events)

                    'Me.Btn_Event_AddNew.Caption = "Add Event to Person"
                    'Me.Btn_Event_AddNew.Enabled = True
                    'Me.Lbl_Events_ViewingFor.Caption = "Viewing Events for: Person no. " & Me.Combo_Events_DataToView & " (" & Me.Combo_Events_DataToView.Column(1) & ")"
                    Return db.GetSQLDataTable(SQL.ToString)
                Case EventView.CaseId
                    'By Case

                    'Select clause
                    SQL = New StringBuilder
                    With SQL
                        '.Append("SELECT D_Event.Event_ID, Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end as EventDT, D_Event.Event_Description, L_Consultant.con_name,")
                        '.Append(" CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + convert(varchar(12),Event_DTScheduled,103) WHEN 3 THEN 'Completed on ' + convert(varchar(12),Event_DTCompleted,103) END AS Category, Case Event_LinkType When 1 then 'Case' when 2 then 'Person' when 3 then 'Company' end as LinkFor")

                        ''From Clause
                        '.Append(" FROM D_Event LEFT OUTER JOIN")
                        '.Append(" L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")

                        ''VB Code Defnition Where Clause (Item ID and Link Type defined above)
                        '.Append(" WHERE (D_Event.Event_LinkType = 1) And (D_Event.Event_LinkID = " & LinkID & ")")

                        ''Order By
                        '.Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc")


                        .Append("SELECT TOP 100 PERCENT Event_ID,  CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END AS EventDT, ")
                        .Append("   Event_Description, ISNULL(ConScheduled, ConEntered) AS con_name, ")
                        .Append("     CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + CONVERT(varchar(12), Event_DTScheduled, 103) ")
                        .Append("  WHEN 3 THEN 'Completed on ' + CONVERT(varchar(12), Event_DTCompleted, 103) END AS Category, ")
                        .Append("  CASE Event_LinkType WHEN 1 THEN 'Case' WHEN 2 THEN 'Person' WHEN 3 THEN 'Company' END AS LinkFor, Event_Message")
                        .Append(" FROM  dbo.V_Events WHERE  (Event_LinkID = " & LinkID & ") AND (Event_LinkType = 1)")
                        .Append(" ORDER BY CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END DESC")

                        'DataBinding.BindListView(.ToString, Me.lvwEvents)
                    End With

                    'Call GblFunc_Populate_List(StrSQL, "MainDisplay_Event_List", Form_Fm_MainDisplay.List_Events)

                    'Me.Btn_Event_AddNew.Caption = "Add Event to Case"
                    'Me.Btn_Event_AddNew.Enabled = True
                    'Me.Lbl_Events_ViewingFor.Caption = "Viewing Events for: Case no. " & Me.Combo_Events_DataToView & " (" & Me.Combo_Events_DataToView.Column(1) & " : " & Me.Combo_Events_DataToView.Column(2) & ")"
                    Return db.GetSQLDataTable(SQL.ToString)
                Case EventView.Consultant

                    Dim myData As DataTable
                    Dim myRow As DataRow
                    Dim CaseList As New StringBuilder
                    Dim PersonList As New StringBuilder

                    'Everything entered by selected consultant

                    'Need to assemble a list of all the Person and Case ref numbers for this company.

                    'Cases first
                    SQL = New StringBuilder
                    With SQL
                        If GlobalCRM.CurrentCompanyID > 0 Then
                            .Append("Select Case_ID from  D_Case where Case_CompanyID = " & GlobalCRM.CurrentCompanyID)
                        Else
                            .Append("Select Case_ID from  D_Case where Case_CandidateID = " & GlobalCRM.CurrentCandidateID)
                        End If

                        myData = db.GetSQLDataTable(.ToString)
                    End With

                    For Each myRow In myData.Rows
                        CaseList.Append(myRow.Item("case_ID") & ",")
                    Next

                    myData.Dispose()

                    'Now people
                    'If Me.Page_Company.Visible = True Then
                    SQL = New StringBuilder
                    SQL.Append("Select Person_ID from  D_Person where Person_CompanyID = " & GlobalCRM.CurrentCompanyID) ' & Me.Txt_Company_ID)
                    myData = db.GetSQLDataTable(SQL.ToString)

                    For Each myRow In myData.Rows


                        PersonList.Append(myRow.Item("Person_ID") & ",")

                    Next
                    'Else
                    'Rst.Close()
                    'End If
                    'Else
                    'StrPersonList = Me.List_Person_People
                    'End If

                    'Now build the query for the event list (combo not needed for everything)

                    'Select clause
                    SQL = New StringBuilder
                    With SQL
                        .Append("SELECT D_Event.Event_ID, Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end as EventDT, D_Event.Event_Description, L_Consultant.con_name,")
                        .Append("CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + convert(varchar(12),Event_DTScheduled,103) WHEN 3 THEN 'Completed on ' + convert(varchar(12),Event_DTCompleted,103) END AS Category, Case Event_LinkType When 1 then 'Case' when 2 then 'Person' when 3 then 'Company' end as LinkFor, Event_Message ")

                        'From Clause
                        .Append("FROM D_Event LEFT OUTER JOIN")
                        .Append(" L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")

                        'VB Code Defnition Where Clause (Item ID and Link Type defined above)

                        If GlobalCRM.CurrentCompanyID > 0 Then
                            .Append(" WHERE ( (D_EVent.Event_linktype =  3 AND D_Event.Event_LinkID = " & LinkID & ")")

                            If PersonList.ToString <> "" Then
                                PersonList = New StringBuilder("(" & Strings.Left(PersonList.ToString, Len(PersonList.ToString) - 1) & ")")
                                .Append(" OR (D_EVent.Event_linktype =  2 AND D_Event.Event_LinkID in " & PersonList.ToString & ")")
                            End If

                        Else
                            .Append(" WHERE ( (D_EVent.Event_linktype =  2 AND D_Event.Event_LinkID = " & LinkID & ")")
                        End If

                        If CaseList.ToString <> "" Then
                            CaseList = New StringBuilder("(" & Strings.Left(CaseList.ToString, Len(CaseList.ToString) - 1) & ")")
                            .Append(" OR (D_EVent.Event_linktype =  1 AND D_Event.Event_LinkID in " & CaseList.ToString & ")")
                        End If

                        .Append(") AND Event_EnteredBy = " & LinkID)

                        'Order By
                        .Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc")
                        'DataBinding.BindListView(.ToString, Me.lvwEvents)
                    End With

                    'Me.Btn_Event_AddNew.Caption = "Add Event"
                    'Me.Btn_Event_AddNew.Enabled = False
                    'Me.Lbl_Events_ViewingFor.Caption = "Viewing Events for: All cases where entered by " & Me.Combo_Events_DataToView.Column(1)
                    Return db.GetSQLDataTable(SQL.ToString)
                Case EventView.Combined
                    'Case combined. All Events for selected case, plus those for the company (if present), candidate and contact (if present)

                    Dim myData As DataTable

                    'Get candidate ID
                    SQL = New StringBuilder

                    SQL.Append("Select Case_CandidateID, Case_ContactID from D_Case where Case_ID = " & LinkID)
                    myData = db.GetSQLDataTable(SQL.ToString)

                    If myData.Rows.Count > 0 Then


                        LngCandidateID = myData.Rows(0).Item("Case_CandidateID")
                        LngContactID = myData.Rows(0).Item("Case_ContactID")
                    End If
                    myData.Dispose()


                    'Select clause
                    SQL = New StringBuilder
                    With SQL
                        .Append("SELECT D_Event.Event_ID, Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end as EventDT, D_Event.Event_Description, L_Consultant.con_name,")
                        .Append(" CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + convert(varchar(12),Event_DTScheduled,103) WHEN 3 THEN 'Completed on ' + convert(varchar(12),Event_DTCompleted,103) END AS Category,")

                        If GlobalCRM.CurrentCompanyID > 0 Then
                            .Append(" Case  ")
                            .Append("When Event_LinkType = 1 then 'Case' ")
                            .Append(" when Event_LinkType = 2 and Event_LinkID = " & LngCandidateID & " then 'Candidate' ")
                            .Append(" when Event_LinkType = 2 and Event_LinkID = " & LngContactID & " then 'Contact' ")
                            .Append(" when Event_LinkType = 2 then 'Person' ")
                            .Append("when Event_LinkType = 3 then 'Company' end as LinkFor, Event_Message")
                        Else
                            .Append(" Case  ")
                            .Append(" When Event_LinkType = 1 then 'Case' ")
                            .Append(" when Event_LinkType = 2 then 'Client' end as LinkFor")
                        End If

                        'From Clause
                        .Append(" FROM D_Event LEFT OUTER JOIN")
                        .Append(" L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")

                        'VB Code Defnition Where Clause (Item ID and Link Type defined above)
                        .Append(" WHERE (D_Event.Event_LinkType = 1) And (D_Event.Event_LinkID = " & LinkID & ")")

                        'Also get candidate events if candidate ID known
                        If LngCandidateID <> 0 Then
                            .Append(" OR (D_Event.Event_LinkType = 2) And (D_Event.Event_LinkID = " & LngCandidateID & ")")
                        End If

                        'is this a company case
                        If GlobalCRM.CurrentCompanyID > 0 Then

                            'Get company events as well
                            .Append(" OR (D_Event.Event_LinkType = 3) And (D_Event.Event_LinkID = " & GlobalCRM.CurrentCompanyID & ")")

                            'if we have a contact, get thier events as well
                            If LngContactID <> 0 Then
                                .Append(" OR (D_Event.Event_LinkType = 2) And (D_Event.Event_LinkID = " & LngContactID & ")")
                            End If
                        End If


                        'Order By
                        .Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc")
                        'DataBinding.BindListView(.ToString, Me.lvwEvents)
                    End With
                    Return db.GetSQLDataTable(SQL.ToString)

                Case EventView.All

                    Dim CaseList() As Long = Nothing
                    Dim myData As DataTable
                    Dim myRow As DataRow
                    Dim i As Integer

                    If GlobalCRM.CurrentCompanyID > 0 Then
                        'Need to assemble a list of all the Person and Case ref numbers for this company.

                        'Cases first

                        Dim PeopleList() As Long = Nothing

                        SQL = New StringBuilder
                        SQL.Append("Select Case_ID from D_Case where Case_CompanyID = " & GlobalCRM.CurrentCompanyID)

                        myData = db.GetSQLDataTable(SQL.ToString)

                        If IsNothing(myData) Then
                            Return Nothing
                            Exit Function
                        End If

                        For Each myRow In myData.Rows
                            ReDim Preserve CaseList(i)
                            CaseList(i) = myRow.Item("case_id")
                            i = i + 1 'Harvey added this to fix problem
                        Next

                        myData.Dispose()

                        'Now people
                        SQL = New StringBuilder
                        SQL.Append("Select Person_ID from  D_Person where Person_CompanyID = " & GlobalCRM.CurrentCompanyID)

                        myData = db.GetSQLDataTable(SQL.ToString)

                        If IsNothing(myData) Then
                            Exit Function
                        End If

                        i = 0

                        For Each myRow In myData.Rows
                            ReDim Preserve PeopleList(i)
                            PeopleList(i) = myRow.Item("person_id")
                            i = i + 1 'Harvey added this to fix problem
                        Next

                        myData.Dispose()

                        'Now build the query for the event list (combo not needed for everything)

                        'Select clause
                        SQL = New StringBuilder
                        With SQL
                            '.Append("SELECT D_Event.Event_ID, Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end as EventDT, D_Event.Event_Description, L_Consultant.con_name,")
                            '.Append(" CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + convert(varchar(12),Event_DTScheduled,103) WHEN 3 THEN 'Completed on ' + convert(varchar(12),Event_DTCompleted,103) END AS Category, Case Event_LinkType When 1 then 'Case' when 2 then 'Person' when 3 then 'Company' end as LinkFor")

                            ''From Clause
                            '.Append(" FROM D_Event LEFT OUTER JOIN")
                            '.Append(" L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")

                            ''VB Code Defnition Where Clause (Item ID and Link Type defined above)
                            '.Append(" WHERE (D_EVent.Event_linktype =  3 AND D_Event.Event_LinkID = " & GlobalCRM.CurrentCompanyID & ")")
                            'If Not CaseList Is Nothing Then
                            '    If CaseList.Length > 0 Then
                            '        .Append(" OR (D_EVent.Event_linktype =  1 AND D_Event.Event_LinkID in " & CommonFunc.ArrayToCSV(CaseList) & ")")
                            '    End If
                            'End If
                            'If Not PeopleList Is Nothing Then
                            '    If PeopleList.Length > 0 Then
                            '        .Append(" OR (D_EVent.Event_linktype =  2 AND D_Event.Event_LinkID in " & CommonFunc.ArrayToCSV(PeopleList) & ")")
                            '    End If
                            'End If
                            ''Order By
                            '.Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc")


                            .Append("SELECT     TOP 100 PERCENT Event_ID,  CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END AS EventDT, ")
                            .Append(" Event_Description, ISNULL(ConScheduled, ConEntered) AS con_name,  CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + CONVERT(varchar(12), Event_DTScheduled, 103) ")
                            .Append(" WHEN 3 THEN 'Completed on ' + CONVERT(varchar(12), Event_DTCompleted, 103) END AS Category,  CASE Event_LinkType WHEN 1 THEN 'Case' WHEN 2 THEN 'Person' WHEN 3 THEN 'Company' END AS LinkFor, Event_Message")


                            'From Clause
                            .Append(" FROM         dbo.V_Events")

                            'VB Code Defnition Where Clause (Item ID and Link Type defined above)
                            .Append(" WHERE (Event_linktype =  3 AND Event_LinkID = " & GlobalCRM.CurrentCompanyID & ")")
                            If Not CaseList Is Nothing Then
                                If CaseList.Length > 0 Then
                                    .Append(" OR (Event_linktype =  1 AND Event_LinkID in " & CommonFuncs.ArrayToCSV(CaseList) & ")")
                                End If
                            End If
                            If Not PeopleList Is Nothing Then
                                If PeopleList.Length > 0 Then
                                    .Append(" OR (Event_linktype =  2 AND Event_LinkID in " & CommonFuncs.ArrayToCSV(PeopleList) & ")")
                                End If
                            End If
                            'Order By
                            .Append(" ORDER BY CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END DESC")

                        End With

                        Return db.GetSQLDataTable(SQL.ToString)

                    Else
                        'Private individual

                        'Only need to assemble case list
                        'Cases first
                        SQL = New StringBuilder
                        'SQL.Append("Select Case_ID from D_Case where Case_CandidateID = " & frmMainDisplay.ActivePersonId)

                        SQL.Append("Select Case_ID from D_Case where Case_CandidateID = " & ActivePersonId)

                        myData = db.GetSQLDataTable(SQL.ToString)

                        If IsNothing(myData) Then
                            Return Nothing
                            Exit Function
                        End If

                        For Each myRow In myData.Rows
                            ReDim Preserve CaseList(i)
                            CaseList(i) = myRow.Item("case_id")
                            i = i + 1 'Harvey added this to fix problem
                        Next

                        myData.Dispose()

                        'Now build the query for the event list (combo not needed for everything)

                        'Select clause
                        SQL = New StringBuilder

                        With SQL
                            '.Append("SELECT D_Event.Event_ID, Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end as EventDT, D_Event.Event_Description, L_Consultant.con_name,")
                            '.Append(" CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + convert(varchar(12),Event_DTScheduled,103) WHEN 3 THEN 'Completed on ' + convert(varchar(12),Event_DTCompleted,103) END AS Category, Case Event_LinkType When 1 then 'Case' when 2 then 'Person' when 3 then 'Company' end as LinkFor")

                            ''From Clause
                            '.Append(" FROM D_Event LEFT OUTER JOIN")
                            '.Append(" L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")

                            ''VB Code Defnition Where Clause (Item ID and Link Type defined above)
                            '.Append(" WHERE (D_EVent.Event_linktype =  2 AND D_Event.Event_LinkID = " & Me.ActivePersonId & ")")

                            'If CaseList.Length > 0 Then
                            '    .Append(" OR (D_EVent.Event_linktype =  1 AND D_Event.Event_LinkID in " & CommonFunc.ArrayToCSV(CaseList) & ")")
                            'End If

                            ''Order By
                            '.Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc")


                            .Append("SELECT TOP 100 PERCENT Event_ID, CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END AS EventDT,Event_Description ")
                            .Append(" , ISNULL(ConScheduled, ConEntered) AS con_name,  CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + CONVERT(varchar(12), Event_DTScheduled, 103) ")

                            'From Clause
                            '                            .Append(" WHEN 3 THEN 'Completed on ' + CONVERT(varchar(12), Event_DTCompleted, 103) END AS Category, CASE Event_LinkType WHEN 1 THEN 'Case' WHEN 2 THEN 'Person' WHEN 3 THEN 'Company' END AS LinkFor, Event_Message FROM dbo.V_Events WHERE (Event_LinkType = 2) AND (Event_LinkID = " & frmMainDisplay.ActivePersonId & ")")

                            .Append(" WHEN 3 THEN 'Completed on ' + CONVERT(varchar(12), Event_DTCompleted, 103) END AS Category, CASE Event_LinkType WHEN 1 THEN 'Case' WHEN 2 THEN 'Person' WHEN 3 THEN 'Company' END AS LinkFor, Event_Message FROM dbo.V_Events WHERE (Event_LinkType = 2) AND (Event_LinkID = " & ActivePersonId & ")")
                            '.Append(" L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")

                            'VB Code Defnition Where Clause (Item ID and Link Type defined above)
                            ' .Append(" WHERE (D_EVent.Event_linktype =  2 AND D_Event.Event_LinkID = " & Me.ActivePersonId & ")")

                            If CaseList.Length > 0 Then
                                .Append(" OR (Event_LinkType = 1) AND (Event_LinkID IN " & CommonFuncs.ArrayToCSV(CaseList) & ")")
                            End If

                            'Order By
                            .Append(" ORDER BY CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END DESC")



                            '.Append("SELECT TOP 100 PERCENT Event_ID, CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END AS EventDT,Event_Description ")
                            '.Append(", ISNULL(ConScheduled, ConEntered) AS con_name,  CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + CONVERT(varchar(12), Event_DTScheduled, 103) ")
                            '.Append("  WHEN 3 THEN 'Completed on ' + CONVERT(varchar(12), Event_DTCompleted, 103) END AS Category, CASE Event_LinkType WHEN 1 THEN 'Case' WHEN 2 THEN 'Person' WHEN 3 THEN 'Company' END AS LinkFor FROM dbo.V_Events WHERE (Event_LinkType = 2) AND (Event_LinkID = " & Me.ActivePersonId & ") OR (Event_LinkType = 1) AND (Event_LinkID IN " & CommonFunc.ArrayToCSV(CaseList) & ")")
                            '.Append("  ORDER BY CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END DESC")
                            '.Append(" FROM dbo.V_Events WHERE   (Event_LinkType = 2) AND (Event_LinkID = " & Me.ActivePersonId & ") OR  (Event_LinkType = 1) AND (Event_LinkID IN " & CommonFunc.ArrayToCSV(CaseList) & ")")
                            '.Append(" ORDER BY CASE Event_Category WHEN 2 THEN Event_Dtscheduled WHEN 3 THEN Event_Dtcompleted ELSE Event_Dtentered END DESC")

                        End With

                        Return db.GetSQLDataTable(SQL.ToString)
                    End If

                    'DataBinding.BindListView(SQL.ToString, Me.lvwEvents)



                    'Me.Btn_Event_AddNew.Caption = "Add Event to Case"
                    'Me.Btn_Event_AddNew.Enabled = True
                    'Me.Lbl_Events_ViewingFor.Caption = "Viewing Events for: Case no. " & Me.Combo_Events_DataToView & " (" & Me.Combo_Events_DataToView.Column(1) & " : " & Me.Combo_Events_DataToView.Column(2) & ")"

            End Select

            'Set up query for Event Dump
            'If StrSQL <> "" Then
            '    StrSQL = Right(StrSQL, Len(StrSQL) - 6)

            '    StrSQL = "Select Event_Message, " & StrSQL
            '    Call GblFunc_Create_Query(StrSQL, "Qry_SQL_Rpt_Maindisplay_EventDump")
            'End If

            Return Nothing
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            Sql = Nothing
        End Try
    End Function

    Private Shared Function SQL_NoEventCases() As String

        Dim SQL As New StringBuilder
        Dim dateTime As String
        dateTime = Format(Date.Now.AddDays(-14), "yyyy-mm-dd hh:mm:ss")
        'Dim dt As Date = Format(dateTime, "yyyy-mm-dd")
        With SQL
            .Append("SELECT     dbo.D_Case.Case_ID, MAX(dbo.D_Event.Event_DTEntered) AS LastEventOn, dbo.L_Consultant.con_name,  ")
            .Append(" dbo.V_Case.Gross, dbo.V_Case.Paid,  dbo.V_Case.Balance FROM         dbo.D_Case INNER JOIN ")
            .Append("  dbo.D_Event ON dbo.D_Case.Case_ID = dbo.D_Event.Event_LinkID INNER JOIN ")
            .Append("  dbo.L_Consultant ON dbo.D_Case.Case_ConSaleID = dbo.L_Consultant.Con_ID INNER JOIN ")
            .Append(" dbo.L_Branch ON dbo.L_Consultant.Con_BranchID = dbo.L_Branch.Branch_ID INNER JOIN ")
            .Append("   dbo.V_Case ON dbo.D_Case.Case_ID = dbo.V_Case.Case_ID GROUP BY dbo.L_Branch.Branch_ID,  ")
            .Append(" dbo.D_Case.Case_ID, dbo.D_Case.Case_StatusID, dbo.L_Consultant.con_name, dbo.V_Case.Balance, dbo.V_Case.Gross, ")
            .Append(" dbo.V_Case.Paid HAVING      (MAX(dbo.D_Event.Event_DTEntered) < = DATEADD(day,-14,GETDATE())) AND ")
            .Append(" (dbo.L_Branch.Branch_ID = " & GlobalCRM.ReportCriteria.Branch_id & ") AND(dbo.D_Case.Case_StatusID > 100 AND dbo.D_Case.Case_StatusID < 1000)")


            '" WHERE " & ReportCriteria_Dates("invoice_date")
        End With

        Return SQL.ToString


    End Function
    Private Shared Function SQL_OpenCasesNoInvoice() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Case.Case_ID, ISNULL(L_Products.Product_Name, 'unspecified') AS Product, L_CaseStages.CaseStage_Title, D_Case.Case_DTStatus,")
            .Append("D_Case.Case_DTSold, ISNULL(L_Consultant_Sales.con_name, 'Unspecified') AS SalesCon, ISNULL(L_Consultant_CW.con_name, 'Unspecified') ")
            .Append("AS CW, ISNULL(D_Company.Company_Name, 'Private Individual') AS Company, V_Case.GrossQuote, V_Case.IncomeQuote ")
            .Append("FROM L_Consultant L_Consultant_CW RIGHT OUTER JOIN ")
            .Append("V_Case RIGHT OUTER JOIN ")
            .Append("D_Case ON V_Case.Case_ID = D_Case.Case_ID LEFT OUTER JOIN ")
            .Append("A_Invoices ON D_Case.Case_ID = A_Invoices.Invoice_CaseID LEFT OUTER JOIN ")
            .Append("L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN ")
            .Append("L_CaseStages ON D_Case.Case_StatusID = L_CaseStages.CaseStage_Number ON ")
            .Append("L_Consultant_CW.Con_ID = D_Case.Case_ConCWID LEFT OUTER JOIN ")
            .Append("L_Consultant L_Consultant_Sales ON D_Case.Case_ConSaleID = L_Consultant_Sales.Con_ID LEFT OUTER JOIN ")
            .Append("D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID  WHERE (D_Case.Case_StatusId Between 100 and 990) AND (Invoice_ID is null) ")
            .Append(ReportCriteria_Consultant("case_consaleid"))
            .Append(" and " & ReportCriteria_Dates("d_case.Case_DTStatus"))
            .Append(ReportCriteria_Product("case_productid") & " ")
            .Append("Order By D_Case.Case_ID")

        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_SalesStatusSummary() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT MAX(L_CaseStages.CaseStage_Title) AS Stage, COUNT(D_Case.Case_ID) AS NoCases, AVG(DATEDIFF(day, D_Case.Case_DTStatus, GETDATE())) ")
            .Append("AS AvgTime, SUM(CASE D_Case.Case_CompanyID WHEN 0 THEN 1 ELSE 0 END) AS NoPrivate ")
            .Append("FROM D_Case LEFT OUTER JOIN ")
            .Append("L_CaseStages ON D_Case.Case_StatusID = L_CaseStages.CaseStage_Number  WHERE  (D_Case.Case_StatusID < 100) GROUP BY D_Case.Case_StatusID ")
            .Append(ReportCriteria_Consultant("case_consaleid"))
            .Append(ReportCriteria_Product("case_productid"))
            .Append("ORDER BY D_Case.Case_StatusID")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_PipeLinePreSale() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Case.Case_ID, V_CaseCharges.GrossQuote, V_CaseCharges.IncomeQuote, isnull(Product_Name,'Unspecified') as Product, L_Consultant.con_name, ISNULL(D_Company.Company_Name, 'Private Individuals') ")
            .Append("AS CoName, L_CaseStages.CaseStage_Title, D_Case.Case_DTStatus, ")
            .Append("ISNULL(D_Person_Contact.Person_FName + ' ' + D_Person_Contact.Person_LName, '') AS Contact, ")
            .Append("ISNULL(D_Person_Contact.Person_FName + ' ' + D_Person_Contact.Person_LName, '') AS Candidate ")
            .Append("FROM D_Person D_Person_Contact RIGHT OUTER JOIN ")
            .Append("L_CaseStages RIGHT OUTER JOIN ")
            .Append("L_Products RIGHT OUTER JOIN ")
            .Append("D_Case INNER JOIN ")
            .Append("V_CaseCharges ON D_Case.Case_ID = V_CaseCharges.Case_ID INNER JOIN ")
            .Append("L_Consultant ON D_Case.Case_ConSaleID = L_Consultant.Con_ID ON L_Products.Product_ID = D_Case.Case_ProductID ON ")
            .Append("L_CaseStages.CaseStage_Number = D_Case.Case_StatusID LEFT OUTER JOIN ")
            .Append("D_Person D_Person_Candidate ON D_Case.Case_CandidateID = D_Person_Candidate.Person_ID ON ")
            .Append("D_Person_Contact.Person_ID = D_Case.Case_ContactID LEFT OUTER JOIN ")
            .Append("D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID  WHERE (D_Case.Case_StatusID < 100) ")
            .Append(ReportCriteria_Consultant("case_consaleid"))
            .Append(" and " & ReportCriteria_Dates("Case_DTStatus"))
            .Append(ReportCriteria_Product("case_productid"))

            '(Case_DTStatus BETWEEN '07/31/2006 23:59:59' AND '08/31/2006 23:59:59')")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_VisasExpiring() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Person.Person_ID AS PersonID,")
            .Append("D_Person.Person_Title + ' ' + D_Person.Person_Fname + ' ' + D_Person.Person_Mname + ' ' + D_Person.Person_Lname AS PersonName, ")
            .Append("L_Country.Country_Nationality AS Nationality, ISNULL(D_Company.Company_Name, 'none') AS CompanYName, ")
            .Append("L_UKVisa.UKVisa_Name AS CurrentVisa, D_Person.Person_ExpiryDT AS VisaExpiry, ISNULL(L_Consultant_1.con_name, 'none') AS AccountManager, ")
            .Append("ISNULL(L_Consultant_1.con_name, 'none') AS PersonalConsultant, D_company.company_ID as CoID ")
            .Append("FROM L_Consultant L_Consultant_1 RIGHT OUTER JOIN ")
            .Append("D_Person INNER JOIN ")
            .Append("L_Country ON D_Person.Person_NatID = L_Country.Country_ID ON L_Consultant_1.Con_ID = D_Person.Person_AccountManager LEFT OUTER JOIN ")
            .Append("L_Consultant L_Consultant_2 RIGHT OUTER JOIN ")
            .Append("D_Company ON L_Consultant_2.Con_ID = D_Company.Company_AccountManagerID ON ")
            .Append("D_Person.Person_CompanyID = D_Company.Company_ID LEFT OUTER JOIN ")
            .Append("L_UKVisa ON D_Person.Person_UKVisaID = L_UKVisa.UKVisa_ID  ")
            .Append("WHERE " & ReportCriteria_Dates("person_expirydt")) '(D_Person.Person_ExpiryDT BETWEEN '07/31/2006 23:59:59' AND '08/31/2006 23:59:59') ORDER BY D_Person.Person_ExpiryDT")

        End With

        Return SQL.ToString


    End Function

    Private Shared Function SQL_Commission() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select c.case_id as payment_caseid, pay.payment_id,pay.payment_amount,c.case_productid,")
            .Append("c.case_companyid as companyid,pay.payment_type,pay.payment_dtentered,c.case_enteredby,")
            .Append("pay.payment_method,ch.defcharge_amount,ch.defcharge_tpcost,ch.defcharge_vat,")
            .Append("dbo.getcommission (ch.defcharge_amount,defcharge_tpcost,defcharge_vat,payment_amount) as commissionable,")
            .Append("dbo.personorcompany(c.case_companyid,c.case_candidateid) as name from d_case c,")
            .Append("a_payments pay, d_defcharges ch, l_products prod ")
            .Append("where c.case_id = pay.payment_caseid And c.case_productid = ch.defcharge_productid ")
            .Append("and c.case_companyid = ch.defcharge_companyid And prod.product_id = c.case_productid ")
            '.Append("and " & Me.ReportCriteria_Consultant("case_consaleid"))
            If Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(ReportCriteria_Consultant("case_consaleid"))
            ElseIf Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(ReportCriteria_Consultant("case_concwid"))
            End If

            If ReportCriteria_Dates("payment_dtentered") <> "" Then
                .Append(" and " & ReportCriteria_Dates("payment_dtentered"))
            End If

        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_CaseStages() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT L_StageDiagram.StageDiagram_ID, L_StageDiagram.StageDiagram_Name , L_StageLinks.StageLink_FromID, L_CaseStages.CaseStage_Title as StageFrom,")
            .Append("L_StageLinks.StageLink_ToID, L_CaseStages_To.CaseStage_Title as StageTo, L_StageLinks.StageLink_Position, Case L_StageLinks.StageLink_CustomerType when 1 then 'Private' when 2 then 'Corporate' else 'All' end as Ctype,")
            .Append("Case L_StageLinks.StageLink_Admin when 1 then 'Admin' else '' end as Admin, L_CaseStages_To.CaseStage_DTColumn, Case L_CaseStages_To.CaseStage_DTOverwrite when 1 then 'Overwrite' else '' end as OverWrite ")
            .Append("FROM L_StageDiagram INNER JOIN L_StageLinks ON L_StageDiagram.StageDiagram_ID = L_StageLinks.StageLink_DiagramID INNER JOIN ")
            .Append("L_CaseStages ON L_StageLinks.StageLink_FromID = L_CaseStages.CaseStage_Number INNER JOIN ")
            .Append("L_CaseStages L_CaseStages_To ON L_StageLinks.StageLink_ToID = L_CaseStages_To.CaseStage_Number")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_WorkInProgress() As String

        Dim SQL As New StringBuilder

        'With SQL
        '    .Append("select c.case_id,case_dtsold,st.casestage_title,pr.product_name,dep.department_name,sum(ch.charge_amount)")
        '    .Append(" from d_case c, l_casestages st, a_charges ch,l_products pr, l_producttypes pt, l_departments dep")
        '    .Append(" where ")
        '    .Append(ReportCriteria_Dates("D_Event.Event_DTEntered"))
        '    .Append(" and c.case_statusid = st.casestage_number")
        '    .Append(" and st.casestage_number < 1000")
        '    .Append(" and c.case_id = ch.charge_caseid")
        '    .Append(" and c.case_productid = pr.product_id")
        '    .Append(" and pr.product_type = pt.producttype_id")
        '    .Append(" and pt.producttype_departmentid = dep.department_id")
        '    .Append(" and c.case_dtsold is not null")
        '    .Append(" group by c.case_id,c.case_dtsold,st.casestage_title,pr.product_name,dep.department_name")
        '    .Append(" order by dep.department_name,case_id")
        '    Return SQL.ToString
        'End With

        With SQL
            .Append("select c.case_id,c.case_dtsold,cs.casestage_title, p.product_name, d.department_name, conSale.con_name,conCW.con_name,")
            .Append("coalesce(convert(varchar(20),sum(ch.charge_amount)),'None Yet!') as Charges ")
            .Append("from l_products p,l_consultant conSale, l_consultant conCW,l_producttypes pt,l_departments d,")
            .Append("d_case c inner join l_casestages cs on c.case_statusid = cs.casestage_number ")
            .Append("left outer join a_charges ch on c.case_id = ch.charge_caseid ")
            .Append("where c.case_statusid between 100 and 999 ")
            .Append("and c.case_productid = p.product_id ")
            .Append("and c.case_consaleid = consale.con_id ")
            .Append("and c.case_concwid = concw.con_id ")
            .Append("and p.product_type = pt.producttype_id ")
            .Append("and pt.producttype_departmentid = d.department_id ")
            .Append("group by c.case_id,c.case_dtsold,cs.casestage_title, p.product_name, d.department_name, conSale.con_name,conCW.con_name ")
            .Append("order by d.department_name")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_PriceList() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT L_Products.Product_Name, MAX(L_ProductTypes.ProductType_Name) AS Type, SUM(D_DefCharges.DefCharge_Amount) AS Net,")
            .Append("SUM(D_DefCharges.DefCharge_TPCost) AS TPCost, SUM(D_DefCharges.DefCharge_VAT / 100 * D_DefCharges.DefCharge_Amount) AS VAT,")
            .Append("SUM(D_DefCharges.DefCharge_VAT / 100 * D_DefCharges.DefCharge_Amount + D_DefCharges.DefCharge_Amount) AS Gross,")
            .Append("SUM(D_DefCharges.DefCharge_Amount - D_DefCharges.DefCharge_TPCost) AS Income ")
            .Append("FROM L_Products INNER JOIN ")
            .Append("L_ProductTypes ON L_Products.Product_Type = L_ProductTypes.ProductType_ID LEFT OUTER JOIN ")
            .Append("D_DefCharges ON L_Products.Product_ID = D_DefCharges.DefCharge_ProductID ")
            .Append(" GROUP BY L_Products.Product_Name")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_EventSummaryConsultant() As String

        Dim SQL As New StringBuilder

        With SQL

            .Append("SELECT D_Event.Event_ID, L_Consultant.con_name, D_Event.Event_Description,")
            .Append("CASE D_Event.Event_Category WHEN 1 THEN 0 ELSE 1 END AS Event_Sched, D_Event.Event_DTEntered,")
            .Append("CASE isnull(L_EventTypes.EventType_ID,0) WHEN 0 THEN 1 ELSE 0 END AS Event_Auto ")
            .Append("FROM D_Event INNER JOIN ")
            .Append("L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID LEFT OUTER JOIN ")
            .Append("L_EventTypes ON D_Event.Event_Description = L_EventTypes.EventType_Name ")
            .Append("WHERE " & ReportCriteria_Dates("D_Event.Event_DTEntered"))
            .Append(ReportCriteria_Consultant("Event_EnteredBy"))
            '.Append(" order by L_Consultant.con_name, D_Event.Event_Description")

        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_InvoiceListV2() As String

        Dim SQL As New StringBuilder

        With SQL
            '.Append("SELECT A_Invoices.Invoice_ID, MAX(A_Invoices.Invoice_Date) AS Inv_Date, MAX(A_Invoices.Invoice_DateDue) AS DateDue, ")
            '.Append("MAX(A_Invoices.Invoice_CaseID) AS CaseID, SUM(A_Charges.Charge_Amount) AS Net, ")
            '.Append("Cast(SUM(A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) as money) AS VAT, ")
            '.Append("cast(SUM(A_Charges.Charge_Amount + (A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100)) as money) AS Gross, ")
            '.Append("MAX(D_Person.Person_FName + ' ' + D_Person.Person_LName) AS FullName, ")
            '.Append("MAX(L_Products.Product_Name) AS Product, MAX(isnull(D_Company.Company_Name,'')) as CompanyName , ")
            '.Append("MAX(isnull(D_Company.Company_ID,'')) as CompanyID,")
            '.Append("sum(a_charges.charge_tpcost) as thirdparty, sum(a_charges.charge_amount - a_charges.charge_tpcost) as income,")
            '.Append("max(isnull(l_consultant.con_name,'')) as consultant ")
            '.Append("FROM A_Invoices INNER JOIN D_Case ON A_Invoices.Invoice_CaseID = D_Case.Case_ID INNER JOIN A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID INNER JOIN D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID INNER JOIN L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID ")
            '.Append("inner join l_consultant on l_consultant.con_id = d_case.case_consaleid INNER JOIN")
            '.Append(" dbo.L_Branch ON dbo.L_Consultant.Con_BranchID = dbo.L_Branch.Branch_ID ")



            .Append("SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_Date) AS Inv_Date, MAX(dbo.A_Invoices.Invoice_DateDue) AS DateDue, ")
            .Append("                      MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, SUM(dbo.A_Charges.Charge_Amount) AS Net, ")
            .Append("                      CAST(SUM(dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS VAT, ")
            .Append("                      CAST(SUM(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS Gross, ")
            .Append("                      MAX(dbo.L_Products.Product_Name) AS Product, MAX(ISNULL(dbo.D_Company.Company_Name, '')) AS CompanyName, ")
            .Append("                      MAX(ISNULL(dbo.D_Company.Company_ID, '')) AS CompanyID, SUM(dbo.A_Charges.Charge_TPCost) AS thirdparty, ")
            .Append("                      SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS income, MAX(ISNULL(dbo.L_Consultant.con_name, '')) AS consultant ")
            .Append(" FROM         dbo.A_Invoices INNER JOIN")
            .Append("                      dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID INNER JOIN dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID INNER JOIN ")
            .Append("                      dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN dbo.L_Consultant ON dbo.L_Consultant.Con_ID = dbo.D_Case.Case_ConSaleID INNER JOIN ")
            .Append("dbo.L_Branch ON dbo.L_Consultant.Con_BranchID = dbo.L_Branch.Branch_ID LEFT OUTER JOIN")
            .Append("                      dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID")
            .Append(" WHERE " & ReportCriteria_Dates("invoice_date"))

            'If Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            '    .Append(ReportCriteria_Consultant("case_consaleid"))
            'ElseIf Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            '    .Append(ReportCriteria_Consultant("case_concwid"))
            'End If

            .Append(ReportCriteria_Product("case_productid"))
            .Append(ReportCriteria_SLA_Accounts)
            .Append(ReportCriteria_Client_Types)

            .Append(" GROUP BY dbo.A_Invoices.Invoice_ID, dbo.D_Case.Case_ConSaleID, dbo.L_Branch.Branch_Name, dbo.L_Branch.Branch_ID ")
            .Append("HAVING      (dbo.L_Branch.Branch_ID = " & GlobalCRM.ReportCriteria.Branch_id & ") ORDER BY dbo.D_Case.Case_ConSaleID")
        End With

        Return SQL.ToString
        'SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_Date) AS Inv_Date, MAX(dbo.A_Invoices.Invoice_DateDue) AS DateDue, 
        '                      MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, SUM(dbo.A_Charges.Charge_Amount) AS Net, 
        '                      CAST(SUM(dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS VAT, 
        '                      CAST(SUM(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS Gross, 
        '                      MAX(dbo.D_Person.Person_FName + ' ' + dbo.D_Person.Person_LName) AS FullName, MAX(dbo.L_Products.Product_Name) AS Product, 
        '                      MAX(ISNULL(dbo.D_Company.Company_Name, '')) AS CompanyName, MAX(ISNULL(dbo.D_Company.Company_ID, '')) AS CompanyID, 
        '                      SUM(dbo.A_Charges.Charge_TPCost) AS thirdparty, SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS income, 
        '                      MAX(ISNULL(dbo.L_Consultant.con_name, '')) AS consultant
        'FROM         dbo.A_Invoices INNER JOIN
        '                      dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID INNER JOIN
        '                      dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID INNER JOIN
        '                      dbo.D_Person ON dbo.D_Case.Case_CandidateID = dbo.D_Person.Person_ID INNER JOIN
        '                      dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN
        '                      dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID INNER JOIN
        '                      dbo.L_Consultant ON dbo.L_Consultant.Con_ID = dbo.D_Case.Case_ConSaleID INNER JOIN
        '                      dbo.L_Branch ON dbo.L_Consultant.Con_BranchID = dbo.L_Branch.Branch_ID
        'WHERE     (dbo.A_Invoices.Invoice_Date BETWEEN '06/01/2007 23:59:59' AND '06/02/2008 23:59:59')
        'GROUP BY dbo.A_Invoices.Invoice_ID, dbo.D_Case.Case_ConSaleID, dbo.L_Branch.Branch_Name, dbo.L_Branch.Branch_ID
        'HAVING      (dbo.L_Branch.Branch_ID = 1)
        'ORDER BY dbo.D_Case.Case_ConSaleID

    End Function

    Private Shared Function SQL_InvoiceList() As String

        'Invoice List By Consultant (202)

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT A_Invoices.Invoice_ID, MAX(A_Invoices.Invoice_Date) AS Inv_Date, MAX(A_Invoices.Invoice_DateDue) AS DateDue, MAX(A_Invoices.Invoice_CaseID) ")
            .Append("AS CaseID, SUM(A_Charges.Charge_Amount) AS Net, SUM(A_Charges.Charge_Amount - A_Charges.Charge_TPCost) AS Income, CAST(SUM(A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) AS money) AS VAT, ")
            .Append("CAST(SUM(A_Charges.Charge_Amount + A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) AS money) AS Gross, ")
            .Append("MAX(D_Person.Person_FName + ' ' + D_Person.Person_LName) AS FullName, MAX(L_Products.Product_Name) AS Product, ")
            .Append("MAX(ISNULL(D_Company.Company_Name, 'Private Individuals')) AS CompanyName, MAX(ISNULL(D_Company.Company_ID, '')) AS CompanyID, ")
            .Append("Max(L_Consultant.con_name) as SaleCon ")
            .Append("from A_Invoices INNER JOIN ")
            .Append("A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID LEFT OUTER JOIN ")
            .Append("D_Case ON A_Invoices.Invoice_CaseID = D_Case.Case_ID LEFT OUTER JOIN ")
            .Append("L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN ")
            .Append("D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID LEFT OUTER JOIN ")
            .Append("L_Consultant ON D_Case.Case_ConSaleID = L_Consultant.Con_ID LEFT OUTER JOIN ")
            .Append("D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID ")
            .Append("WHERE ") ' (Invoice_Date BETWEEN '06/03/2006 23:59:59' AND '06/10/2006 23:59:59') 
            .Append(ReportCriteria_Dates("invoice_date"))
            '.Append(Me.ReportCriteria_Consultant("case_consaleid"))
            If Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(ReportCriteria_Consultant("case_consaleid"))
            ElseIf Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(ReportCriteria_Consultant("case_concwid"))
            End If

            .Append(ReportCriteria_Product("case_productid"))
            .Append(ReportCriteria_SLA_Accounts)
            .Append(ReportCriteria_Client_Types)

            .Append(" GROUP BY A_Invoices.Invoice_ID ORDER BY A_Invoices.Invoice_ID")
        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_LeadsPerConsultant() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select con_id,con_name,product_name,count(*) as numleads from d_case, l_consultant, l_products ")
            .Append("where d_case.case_enteredby = 'web' and d_case.case_consaleid = l_consultant.con_id and d_case.case_productid = l_products.product_id ")
            .Append(ReportCriteria_Consultant("case_consaleid"))
            If ReportCriteria_Dates("case_dtentered") <> "" Then
                .Append(" and " & ReportCriteria_Dates("case_dtentered"))
            End If
            .Append(" group by con_id,con_name,product_name")
        End With

        Return SQL.ToString

    End Function
    Private Shared Function SQL_CashUp() As String
        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT     TOP 100 PERCENT dbo.A_Payments.Payment_ID, CASE Payment_CompanyID WHEN 0 THEN '' ELSE Payment_CompanyID END AS CompanyID, ")
            .Append("                      dbo.A_Payments.Payment_CaseID, dbo.A_Payments.Payment_Type, dbo.A_Payments.Payment_Amount, dbo.A_Payments.Payment_DTEntered, ")
            .Append("                      dbo.A_Payments.Payment_EnteredBy, dbo.A_Payments.Payment_Method, ")
            .Append("                      CASE Payment_CompanyID WHEN 0 THEN Person_FName + ' ' + Person_LName ELSE Company_Name END AS Name, ")
            .Append("                      dbo.L_ProductTypes.ProductType_Name FROM         dbo.D_Person RIGHT OUTER JOIN                      dbo.A_Payments INNER JOIN")



            .Append("                      dbo.D_Case ON dbo.A_Payments.Payment_CaseID = dbo.D_Case.Case_ID INNER JOIN                       dbo.L_Products INNER JOIN")
            .Append("                      dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID ON ")
            .Append("                      dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN                      dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID ON ")
            .Append("                      dbo.D_Person.Person_ID = dbo.D_Case.Case_CandidateID")
            .Append(" where  (" & ReportCriteria_Dates("dbo.A_Payments.Payment_DTEntered"))

            .Append(") and      (dbo.L_ProductTypes.ProductType_BranchID = " & GlobalCRM.ReportCriteria.Branch_id & ")  ")
            .Append("ORDER BY CASE Payment_CompanyID WHEN 0 THEN '' ELSE Payment_CompanyID END, dbo.A_Payments.Payment_DTEntered")
        End With

        Return SQL.ToString

    End Function

    'Private Shared Function SQL_CashUp() As String

    '    Dim SQL As New StringBuilder

    '    With SQL
    '        .Append("SELECT  A_Payments.Payment_ID, CASE Payment_CompanyID WHEN 0 THEN '' ELSE Payment_CompanyID END AS CompanyID,")
    '        .Append("A_Payments.Payment_caseID, A_Payments.Payment_Type, A_Payments.Payment_Amount, A_Payments.Payment_DTentered,")
    '        .Append("A_Payments.Payment_enteredBy, A_Payments.Payment_Method,")
    '        .Append("CASE Payment_CompanyID WHEN 0 THEN Person_FName + ' ' + Person_LName ELSE Company_Name END AS Name ")
    '        .Append("FROM ((A_Payments INNER JOIN D_Case ON A_Payments.Payment_CaseID = D_Case.Case_ID) LEFT JOIN D_Company ")
    '        .Append("ON D_Case.Case_CompanyID = D_Company.Company_ID) LEFT JOIN D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID ")
    '        .Append("WHERE " & ReportCriteria_Dates("A_Payments.Payment_Dtentered"))
    '        '.Append(Me.ReportCriteria_Consultant("case_consaleid"))
    '        If Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
    '            .Append(ReportCriteria_Consultant("case_consaleid"))
    '        ElseIf Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
    '            .Append(ReportCriteria_Consultant("case_concwid"))
    '        End If

    '        .Append(ReportCriteria_Product("case_productid"))
    '        .Append(ReportCriteria_SLA_Accounts)
    '        .Append(ReportCriteria_Client_Types)
    '        .Append(" ORDER BY CASE Payment_CompanyID WHEN 0 THEN '' ELSE Payment_CompanyID END, A_Payments.Payment_DTEntered")

    '    End With

    '    Return SQL.ToString

    'End Function

    Private Shared ReadOnly Property Criteria() As ReportCriteria
        Get
            'Return HttpContext.Current.Session("ReportCriteria")
            Return GlobalCRM.ReportCriteria
            Return Nothing
        End Get
    End Property

    Private Shared Function ReportCriteria_Client_Types() As String

        Dim SQL As String = String.Empty

        With Criteria

            Select Case .Client_Type

                Case ClientTypes.All
                    'Do nothing

                Case ClientTypes.CorporateOnly

                    SQL = "Case_CompanyID > 0 "

                Case ClientTypes.PrivateOnly

                    SQL = "Case_CompanyID = 0 "

            End Select

        End With

        If SQL.Length > 0 Then
            SQL = " and " & SQL
        End If

        Return SQL

    End Function

    Private Shared Function ReportCriteria_SLA_Accounts() As String

        Dim SQL As String = String.Empty

        With Criteria
            Select Case .Account_Types

                Case AccountTypes.All
                    'Do nothing

                Case AccountTypes.SLA

                    SQL = "Company_AccountType = 2 "

                Case AccountTypes.ExcludeSLA
                    SQL = "(Company_AccountType <> 2 OR Case_CompanyID = 0) "

            End Select
        End With

        If SQL.Length > 0 Then
            SQL = " and " & SQL
        End If

        Return SQL


    End Function

    Private Shared Function ReportCriteria_Dates(ByVal DateCol As String) As String

        Dim SQL As String = String.Empty

        With GlobalCRM.ReportCriteria

            If IsDate(.StartDate) And IsDate(.EndDate) Then
                'SQL = " payment_dtentered between '" & .StartDate & "' and '" & .EndDate & "'"
                'SQL = " invoice_date between '" & .StartDate & "' and '" & .EndDate & "'"
                SQL = " " & DateCol & " between '" & .StartDate & "' and '" & .EndDate & "'"
            End If

        End With

        'If SQL.Length > 0 Then
        '    SQL = " and " & SQL
        'End If

        Return SQL

    End Function

    Private Shared Function ReportCriteria_Product(ByVal ProductCol As String) As String
        Dim db As New CRM.DataLayer.DataAccess
        Try


            Dim SQL As String = String.Empty

            Select Case Criteria.Product_Selection

                Case ProductSelection.All
                    'No SQL needed

                Case ProductSelection.By_Department

                    'Need to keep SQL as simple as possible as this function is reused for many queries

                    Dim Str As New StringBuilder
                    Dim Ids As New DataTable
                    Dim IDList As String = String.Empty
                    Dim Row As DataRow

                    With Str
                        .Append("SELECT L_Products.Product_ID")
                        .Append(" FROM L_Products INNER JOIN")
                        .Append(" L_ProductTypes ON L_Products.Product_Type = L_ProductTypes.ProductType_ID")
                        .Append(" WHERE L_ProductTypes.ProductType_DepartmentID = " & Criteria.Product_Selection_Id)
                    End With

                    Ids = db.GetSQLDataTable(Str.ToString)

                    For Each Row In Ids.Rows

                        If IDList.Length > 0 Then
                            IDList = IDList & ","
                        End If

                        IDList = IDList & Row.Item("product_id")

                    Next

                    If IDList.Length > 0 Then

                        SQL = " " & ProductCol & " in (" & IDList & ")"
                    End If

                Case ProductSelection.By_Product_Type

                    Dim Str As New StringBuilder
                    Dim IdList As String = String.Empty
                    Dim Ids As New DataTable
                    Dim Row As DataRow

                    With Str
                        .Append("Select Product_ID from L_Products where Product_Type IN (" & Criteria.Product_Selection_Id & ")")
                    End With

                    Ids = db.GetSQLDataTable(Str.ToString)

                    For Each Row In Ids.Rows

                        If IdList.Length > 0 Then
                            IdList = IdList & ","
                        End If

                        IdList = IdList & Row.Item("product_id")

                    Next

                    SQL = " " & ProductCol & " in (" & IdList & ")"


            End Select

            If SQL.Length > 0 Then
                SQL = " and " & SQL
            End If

            Return SQL


        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function

    Private Shared Function ReportCriteria_Consultant(ByVal ConsultCol As String) As String

        Dim SQL As String = String.Empty

        With Criteria

            If Not .Consultant_All Then

                If .Consultant_Id > 0 Then

                    'If .Consultant_Role = ConsultantRoles.SoldBy Then
                    '    SQL = " case_consaleid = " & Me.Criteria.Consultant_Id
                    'ElseIf .Consultant_Role = ConsultantRoles.CaseWorker Then
                    '    SQL = " case_concwid = " & Me.Criteria.Consultant_Id
                    'End If
                    SQL = SQL & " " & ConsultCol & " = " & Criteria.Consultant_Id

                ElseIf .Consultant_DeptId > 0 Then

                    'If .Consultant_Role = ConsultantRoles.SoldBy Then
                    '    SQL = " case_consaleid in (" & Me.ConsultantsInDepartment(.Consultant_DeptId) & ")"
                    'ElseIf .Consultant_Role = ConsultantRoles.CaseWorker Then
                    '    SQL = " case_concwid in (" & Me.ConsultantsInDepartment(.Consultant_DeptId) & ")"
                    'End If
                    SQL = SQL & " " & ConsultCol & " in (" & ConsultantsInDepartment(.Consultant_DeptId) & ")"
                End If
            End If

        End With

        If SQL.Length > 0 Then
            SQL = " and " & SQL
        End If

        Return SQL


    End Function

    Private Shared Function ConsultantsInDepartment(ByVal DepartmentId As Integer) As String
        Dim db As New CRM.DataLayer.DataAccess
        Try


            Dim SQL As String
            Dim Ids As DataTable
            Dim Row As DataRow
            Dim IdList As String = String.Empty

            SQL = "select con_ID from l_consultant where con_department = " & DepartmentId

            Ids = db.GetSQLDataTable(SQL)

            For Each Row In Ids.Rows

                If IdList.Length > 0 Then
                    IdList = IdList & ","
                End If

                IdList = IdList & Row.Item("con_id")

            Next

            Return IdList
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function

    Private Shared Function GenerateConversionConReport(ByVal LeadsSQL As String, ByVal SalesSQL As String) As DataTable
        Dim db As New CRM.DataLayer.DataAccess
        Dim myLeads As DataTable
        Dim mySales As DataTable
        Try

            Dim myRow, myRow2 As DataRow

            myLeads = db.GetSQLDataTable(LeadsSQL)
            mySales = db.GetSQLDataTable(SalesSQL)

            'myLeads.Columns.Add(New DataColumn("TotalSales", System.Type.GetType("System.Int16")))

            For Each myRow In mySales.Rows

                'Find consultant in leads and copy sales figure into new column
                For Each myRow2 In myLeads.Rows
                    If myRow.Item("con_name") = myRow2.Item("con_name") Then ' And myRow.Item("department_name") = myRow2.Item("department_name") Then
                        myRow2.Item("totalsales") = myRow("totalsales")
                        myRow2.Item("convrate") = (100 / myRow2.Item("totalleads")) * myRow("totalsales")

                        Exit For
                    End If
                Next


            Next

            Return myLeads
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            myLeads = Nothing
            mySales = Nothing
        End Try


    End Function

    Private Shared Function GenerateConversionProdReport(ByVal LeadsSQL As String, ByVal SalesSQL As String) As DataTable
        Dim db As New CRM.DataLayer.DataAccess
        Dim myLeads As DataTable
        Dim mySales As DataTable
        Try


            Dim myRow, myRow2 As DataRow

            myLeads = DB.GetSQLDataTable(LeadsSQL)
            mySales = DB.GetSQLDataTable(SalesSQL)

            'myLeads.Columns.Add(New DataColumn("TotalSales", System.Type.GetType("System.Int16")))

            For Each myRow In mySales.Rows

                'Find consultant in leads and copy sales figure into new column
                For Each myRow2 In myLeads.Rows
                    If myRow.Item("product_name") = myRow2.Item("product_name") And myRow.Item("branch_name") = myRow2.Item("branch_name") Then
                        myRow2.Item("totalsales") = myRow("totalsales")
                        myRow2.Item("convrate") = (100 / myRow2.Item("totalleads")) * myRow("totalsales")

                        Exit For
                    End If
                Next


            Next

            Return myLeads

        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            myLeads = Nothing
            mySales = Nothing
        End Try

    End Function
    Private Shared Function SQL_ProformaCompany(ByVal CaseId As Long) As String

        Dim SQL As New StringBuilder

        With SQL
            '.Append("SELECT L_Products.Product_Name, D_Case.Case_ID, A_Charges.Charge_Type, A_Charges.Charge_Amount, A_Charges.Charge_VAT, ")
            '.Append(" D_Company.Company_Name, isnull(CASE address_text WHEN '' THEN '' ELSE address_text + char(13) + char(10)")
            '.Append(" END + CASE address_region WHEN '' THEN '' ELSE address_region + char(13) + char(10)")
            '.Append(" END + CASE address_postcode WHEN '' THEN '' ELSE address_postcode + char(13) + char(10)")
            '.Append(" END + CASE address_country WHEN '' THEN '' ELSE address_country + char(13) + char(10) END, '') AS Address,")
            '.Append(" ISNULL(D_Person_Contact.Person_FName + ' ' + D_Person_Contact.Person_LName, '') AS Contact,")
            '.Append(" ISNULL(D_Person_Candidate.Person_FName + ' ' + D_Person_Candidate.Person_LName, '') AS Candidate, A_Charges.Charge_InvoiceID")
            '.Append(" FROM         D_Person D_Person_Contact RIGHT OUTER JOIN")
            '.Append(" A_Charges INNER JOIN")
            '.Append(" D_Case ON A_Charges.Charge_CaseID = D_Case.Case_ID INNER JOIN")
            '.Append(" D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID INNER JOIN")
            '.Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN")
            '.Append(" D_Person D_Person_Candidate ON D_Case.Case_CandidateID = D_Person_Candidate.Person_ID ON")
            '.Append(" D_Person_Contact.Person_ID = D_Case.Case_ContactID LEFT OUTER JOIN")
            '.Append(" D_Address INNER JOIN")
            '.Append(" D_AddressLink ON D_Address.Address_ID = D_AddressLink.AddressLink_AddressID ON")
            '.Append(" D_Company.Company_ID = D_AddressLink.AddressLink_CompanyID")
            '.Append(" WHERE (D_AddressLink.AddressLink_Type IN (2, 3)) AND (A_Charges.Charge_InvoiceID = 0) AND (D_Case.Case_ID = " & CaseId & ") OR")
            '.Append(" (D_AddressLink.AddressLink_ID IS NULL) AND (A_Charges.Charge_InvoiceID = 0) AND (D_Case.Case_ID = " & CaseId & ")")

            .Append(" SELECT     dbo.L_Products.Product_Name, dbo.D_Case.Case_ID, dbo.A_Charges.Charge_Type, dbo.A_Charges.Charge_Amount, dbo.A_Charges.Charge_VAT, ")
            .Append(" dbo.D_Company.Company_Name, ISNULL(CASE address_text WHEN '' THEN '' ELSE address_text + char(13) + char(10) ")
            .Append(" END + CASE address_region WHEN '' THEN '' ELSE address_region + char(13) + char(10) ")
            .Append(" END + CASE address_postcode WHEN '' THEN '' ELSE address_postcode + char(13) + char(10) ")
            .Append(" END + CASE address_country WHEN '' THEN '' ELSE address_country + char(13) + char(10) END, '') AS Address, ")
            .Append(" ISNULL(D_Person_Contact.Person_FName + ' ' + D_Person_Contact.Person_LName, '') AS Contact, ")
            .Append(" ISNULL(D_Person_Candidate.Person_FName + ' ' + D_Person_Candidate.Person_LName, '') AS Candidate, dbo.A_Charges.Charge_InvoiceID, ")
            .Append(" dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100 AS charge_gross, ")
            .Append(" dbo.L_Country.Country_Symbol, dbo.L_Country.Country_CurrencyDesc, dbo.L_Branch.Branch_BankDetails AS bankdetails, ")
            .Append(" dbo.L_Branch.Branch_BranchFooter AS branchdetails")
            .Append(" FROM         dbo.D_Person AS D_Person_Candidate RIGHT OUTER JOIN")
            .Append(" dbo.L_ProductTypes INNER JOIN")
            .Append(" dbo.A_Charges INNER JOIN")
            .Append(" dbo.D_Case ON dbo.A_Charges.Charge_CaseID = dbo.D_Case.Case_ID INNER JOIN")
            .Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID ON ")
            .Append(" dbo.L_ProductTypes.ProductType_ID = dbo.L_Products.Product_Type INNER JOIN")
            .Append(" dbo.L_Country INNER JOIN")
            .Append(" dbo.L_Branch ON dbo.L_Country.Country_ID = dbo.L_Branch.Branch_CountryID ON ")
            .Append(" dbo.L_ProductTypes.ProductType_BranchID = dbo.L_Branch.Branch_ID ON ")
            .Append(" D_Person_Candidate.Person_ID = dbo.D_Case.Case_CandidateID LEFT OUTER JOIN")
            .Append(" dbo.D_Person AS D_Person_Contact ON dbo.D_Case.Case_ContactID = D_Person_Contact.Person_ID LEFT OUTER JOIN")
            .Append(" dbo.D_Address INNER JOIN")
            .Append(" dbo.D_AddressLink ON dbo.D_Address.Address_ID = dbo.D_AddressLink.AddressLink_AddressID ON ")
            .Append(" dbo.D_Company.Company_ID = dbo.D_AddressLink.AddressLink_CompanyID")
            .Append(" WHERE     (dbo.D_AddressLink.AddressLink_Type IN (2, 3)) AND (dbo.A_Charges.Charge_InvoiceID = 0) AND (dbo.D_Case.Case_ID = " & CaseId & ") OR")
            .Append(" (dbo.A_Charges.Charge_InvoiceID = 0) AND (dbo.D_Case.Case_ID = " & CaseId & ") AND (dbo.D_AddressLink.AddressLink_ID IS NULL)")


        End With

        Return SQL.ToString

    End Function

    Private Shared Function SQL_ProformaIndividual(ByVal CaseId As Long) As String

        Dim SQL As New StringBuilder

        With Sql
            '.Append(" SELECT L_Products.Product_Name, D_Case.Case_ID, A_Charges.Charge_Type, A_Charges.Charge_Amount, A_Charges.Charge_VAT, A_Charges.Charge_Amount + (A_Charges.Charge_Amount * A_Charges.Charge_VAT/100) as charge_gross")
            '.Append(" ,ISNULL(D_Person_Candidate.Person_FName + ' ' + D_Person_Candidate.Person_LName, '') AS Candidate, A_Charges.Charge_InvoiceID")
            '.Append(" ,isnull(CASE address_text WHEN '' THEN '' ELSE address_text + char(13) + char(10)")
            '.Append(" END + CASE address_region WHEN '' THEN '' ELSE address_region + char(13) + char(10)")
            '.Append(" END + CASE address_postcode WHEN '' THEN '' ELSE address_postcode + char(13) + char(10)")
            '.Append(" END + CASE address_country WHEN '' THEN '' ELSE address_country + char(13) + char(10) END, '') AS Address")
            '.Append(" FROM D_Address INNER JOIN")
            '.Append(" D_AddressLink ON D_Address.Address_ID = D_AddressLink.AddressLink_AddressID RIGHT OUTER JOIN")
            '.Append(" D_Person D_Person_Candidate ON D_AddressLink.AddressLink_PersonID = D_Person_Candidate.Person_ID RIGHT OUTER JOIN")
            '.Append(" A_Charges INNER JOIN")
            '.Append(" D_Case ON A_Charges.Charge_CaseID = D_Case.Case_ID INNER JOIN")
            '.Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID ON D_Person_Candidate.Person_ID = D_Case.Case_CandidateID")
            '.Append(" WHERE (D_AddressLink.AddressLink_Type IN (2, 3)) AND (A_Charges.Charge_InvoiceID = 0) AND (D_Case.Case_ID = " & CaseId & ") OR")
            '.Append(" (A_Charges.Charge_InvoiceID = 0) AND (D_Case.Case_ID = " & CaseId & ") AND (D_AddressLink.AddressLink_ID IS NULL)")


            '.Append("SELECT     dbo.L_Products.Product_Name, dbo.D_Case.Case_ID, dbo.A_Charges.Charge_Type, dbo.A_Charges.Charge_Amount, dbo.A_Charges.Charge_VAT, ")
            '.Append(" dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100 AS charge_gross, ")
            '.Append(" ISNULL(D_Person_Candidate.Person_FName + ' ' + D_Person_Candidate.Person_LName, '') AS Candidate, dbo.A_Charges.Charge_InvoiceID, ")
            '.Append(" ISNULL(CASE address_text WHEN '' THEN '' ELSE address_text + char(13) + char(10) ")
            '.Append(" END + CASE address_region WHEN '' THEN '' ELSE address_region + char(13) + char(10) ")
            '.Append(" END + CASE address_postcode WHEN '' THEN '' ELSE address_postcode + char(13) + char(10) ")
            '.Append(" END + CASE address_country WHEN '' THEN '' ELSE address_country + char(13) + char(10) END, '') AS Address, ")
            '.Append(" dbo.L_Branch.Branch_BankDetails AS bankdetails, dbo.L_Branch.Branch_InvoiceFooter AS branchdetails")
            '.Append(" FROM         dbo.L_ProductTypes INNER JOIN")
            '.Append(" dbo.A_Charges INNER JOIN")
            '.Append(" dbo.D_Case ON dbo.A_Charges.Charge_CaseID = dbo.D_Case.Case_ID INNER JOIN")
            '.Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID ON ")
            '.Append(" dbo.L_ProductTypes.ProductType_ID = dbo.L_Products.Product_Type INNER JOIN")
            '.Append(" dbo.L_Branch ON dbo.L_ProductTypes.ProductType_BranchID = dbo.L_Branch.Branch_ID LEFT OUTER JOIN")
            '.Append(" dbo.D_Address INNER JOIN")
            '.Append(" dbo.D_AddressLink ON dbo.D_Address.Address_ID = dbo.D_AddressLink.AddressLink_AddressID RIGHT OUTER JOIN")
            '.Append(" dbo.D_Person AS D_Person_Candidate ON dbo.D_AddressLink.AddressLink_PersonID = D_Person_Candidate.Person_ID ON ")
            '.Append(" dbo.D_Case.Case_CandidateID = D_Person_Candidate.Person_ID")
            '.Append(" WHERE     (dbo.D_AddressLink.AddressLink_Type IN (2, 3)) AND (dbo.A_Charges.Charge_InvoiceID = 0) AND (dbo.D_Case.Case_ID = " & CaseId & ") OR")
            '.Append(" (dbo.A_Charges.Charge_InvoiceID = 0) AND (dbo.D_Case.Case_ID = " & CaseId & ") AND (dbo.D_AddressLink.AddressLink_ID IS NULL)")



            .Append(" SELECT     dbo.L_Products.Product_Name, dbo.D_Case.Case_ID, dbo.A_Charges.Charge_Type, dbo.A_Charges.Charge_Amount, dbo.A_Charges.Charge_VAT, ")
            .Append(" dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100 AS charge_gross, ")
            .Append(" ISNULL(D_Person_Candidate.Person_FName + ' ' + D_Person_Candidate.Person_LName, '') AS Candidate, dbo.A_Charges.Charge_InvoiceID, ")
            .Append(" ISNULL(CASE address_text WHEN '' THEN '' ELSE address_text + char(13) + char(10) ")
            .Append(" END + CASE address_region WHEN '' THEN '' ELSE address_region + char(13) + char(10) ")
            .Append(" END + CASE address_postcode WHEN '' THEN '' ELSE address_postcode + char(13) + char(10) ")
            .Append(" END + CASE address_country WHEN '' THEN '' ELSE address_country + char(13) + char(10) END, '') AS Address, ")
            .Append(" dbo.L_Branch.Branch_BankDetails AS bankdetails, dbo.L_Branch.Branch_BranchFooter AS branchdetails, dbo.L_Country.Country_Symbol, ")
            .Append(" dbo.L_Country.Country_CurrencyDesc")
            .Append(" FROM         dbo.L_ProductTypes INNER JOIN")
            .Append(" dbo.A_Charges INNER JOIN")
            .Append(" dbo.D_Case ON dbo.A_Charges.Charge_CaseID = dbo.D_Case.Case_ID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID ON ")
            .Append(" dbo.L_ProductTypes.ProductType_ID = dbo.L_Products.Product_Type INNER JOIN")
            .Append(" dbo.L_Branch ON dbo.L_ProductTypes.ProductType_BranchID = dbo.L_Branch.Branch_ID INNER JOIN")
            .Append(" dbo.L_Country ON dbo.L_Branch.Branch_CountryID = dbo.L_Country.Country_ID LEFT OUTER JOIN")
            .Append(" dbo.D_Address INNER JOIN")
            .Append(" dbo.D_AddressLink ON dbo.D_Address.Address_ID = dbo.D_AddressLink.AddressLink_AddressID RIGHT OUTER JOIN")
            .Append(" dbo.D_Person AS D_Person_Candidate ON dbo.D_AddressLink.AddressLink_PersonID = D_Person_Candidate.Person_ID ON ")
            .Append(" dbo.D_Case.Case_CandidateID = D_Person_Candidate.Person_ID")
            .Append(" WHERE     (dbo.D_AddressLink.AddressLink_Type IN (2, 3)) AND (dbo.A_Charges.Charge_InvoiceID = 0) AND (dbo.D_Case.Case_ID = " & CaseId & ") OR")
            .Append(" (dbo.A_Charges.Charge_InvoiceID = 0) AND (dbo.D_Case.Case_ID = " & CaseId & ") AND (dbo.D_AddressLink.AddressLink_ID IS NULL)")

        End With

        Return SQL.ToString

    End Function

    'Private Shared Function SQL_ProformaCompany(ByVal CaseId As Long) As String

    '    Dim SQL As New StringBuilder

    '    With SQL
    '        .Append("SELECT L_Products.Product_Name, D_Case.Case_ID, A_Charges.Charge_Type, A_Charges.Charge_Amount, A_Charges.Charge_VAT, ")
    '        .Append(" D_Company.Company_Name, isnull(CASE address_text WHEN '' THEN '' ELSE address_text + char(13) + char(10)")
    '        .Append(" END + CASE address_region WHEN '' THEN '' ELSE address_region + char(13) + char(10)")
    '        .Append(" END + CASE address_postcode WHEN '' THEN '' ELSE address_postcode + char(13) + char(10)")
    '        .Append(" END + CASE address_country WHEN '' THEN '' ELSE address_country + char(13) + char(10) END, '') AS Address,")
    '        .Append(" ISNULL(D_Person_Contact.Person_FName + ' ' + D_Person_Contact.Person_LName, '') AS Contact,")
    '        .Append(" ISNULL(D_Person_Candidate.Person_FName + ' ' + D_Person_Candidate.Person_LName, '') AS Candidate, A_Charges.Charge_InvoiceID")
    '        .Append(" FROM         D_Person D_Person_Contact RIGHT OUTER JOIN")
    '        .Append(" A_Charges INNER JOIN")
    '        .Append(" D_Case ON A_Charges.Charge_CaseID = D_Case.Case_ID INNER JOIN")
    '        .Append(" D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID INNER JOIN")
    '        .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN")
    '        .Append(" D_Person D_Person_Candidate ON D_Case.Case_CandidateID = D_Person_Candidate.Person_ID ON")
    '        .Append(" D_Person_Contact.Person_ID = D_Case.Case_ContactID LEFT OUTER JOIN")
    '        .Append(" D_Address INNER JOIN")
    '        .Append(" D_AddressLink ON D_Address.Address_ID = D_AddressLink.AddressLink_AddressID ON")
    '        .Append(" D_Company.Company_ID = D_AddressLink.AddressLink_CompanyID")
    '        .Append(" WHERE (D_AddressLink.AddressLink_Type IN (2, 3)) AND (A_Charges.Charge_InvoiceID = 0) AND (D_Case.Case_ID = " & CaseId & ") OR")
    '        .Append(" (D_AddressLink.AddressLink_ID IS NULL) AND (A_Charges.Charge_InvoiceID = 0) AND (D_Case.Case_ID = " & CaseId & ")")
    '    End With

    '    Return SQL.ToString

    'End Function

    'Private Shared Function SQL_ProformaIndividual(ByVal CaseId As Long) As String

    '    Dim SQL As New StringBuilder

    '    With Sql
    '        .Append(" SELECT L_Products.Product_Name, D_Case.Case_ID, A_Charges.Charge_Type, A_Charges.Charge_Amount, A_Charges.Charge_VAT, ")
    '        .Append(" ISNULL(D_Person_Candidate.Person_FName + ' ' + D_Person_Candidate.Person_LName, '') AS Candidate, A_Charges.Charge_InvoiceID")
    '        .Append(" ,isnull(CASE address_text WHEN '' THEN '' ELSE address_text + char(13) + char(10)")
    '        .Append(" END + CASE address_region WHEN '' THEN '' ELSE address_region + char(13) + char(10)")
    '        .Append(" END + CASE address_postcode WHEN '' THEN '' ELSE address_postcode + char(13) + char(10)")
    '        .Append(" END + CASE address_country WHEN '' THEN '' ELSE address_country + char(13) + char(10) END, '') AS Address")
    '        .Append(" FROM D_Address INNER JOIN")
    '        .Append(" D_AddressLink ON D_Address.Address_ID = D_AddressLink.AddressLink_AddressID RIGHT OUTER JOIN")
    '        .Append(" D_Person D_Person_Candidate ON D_AddressLink.AddressLink_PersonID = D_Person_Candidate.Person_ID RIGHT OUTER JOIN")
    '        .Append(" A_Charges INNER JOIN")
    '        .Append(" D_Case ON A_Charges.Charge_CaseID = D_Case.Case_ID INNER JOIN")
    '        .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID ON D_Person_Candidate.Person_ID = D_Case.Case_CandidateID")
    '        .Append(" WHERE (D_AddressLink.AddressLink_Type IN (2, 3)) AND (A_Charges.Charge_InvoiceID = 0) AND (D_Case.Case_ID = " & CaseId & ") OR")
    '        .Append(" (A_Charges.Charge_InvoiceID = 0) AND (D_Case.Case_ID = " & CaseId & ") AND (D_AddressLink.AddressLink_ID IS NULL)")
    '    End With

    '    Return SQL.ToString

    'End Function
    Private Shared Function SQL_InvoiceReport(ByVal CaseId As Long) As String

        Dim SQL As New StringBuilder

        If GlobalCRM.CurrentCompanyID > 0 Then
            With SQL
                .Append("SELECT A_Invoices.Invoice_ID as Charge_InvoiceID, A_Invoices.Invoice_Date, A_Invoices.Invoice_DateDue as Invoice_DueDate , L_Products.Product_Name, D_Case.Case_ID, A_Charges.Charge_Type,")
                .Append(" A_Charges.Charge_Amount, A_Charges.Charge_VAT, D_Company.Company_Name")
                .Append(" , isnull(CASE address_text WHEN '' THEN '' ELSE address_text + char(13) + char(10) END + CASE address_region WHEN '' THEN '' ELSE address_region + ', ' END + CASE address_postcode WHEN '' THEN '' ELSE address_postcode + char(13) + char(10) END + CASE address_country WHEN '' THEN '' when 'UNITED KINGDOM' then '' ELSE address_country + char(13) + char(10) END,'') AS Address")
                .Append(" , isnull(D_Person_Contact.Person_Fname + ' ' + D_Person_Contact.Person_Lname,'') as Contact")
                .Append(" , isnull(D_Person_Candidate.Person_Fname + ' ' + D_Person_Candidate.Person_Lname,'') as Candidate")
                .Append(" FROM  D_Person D_Person_Contact RIGHT OUTER JOIN")
                .Append(" A_Invoices INNER JOIN")
                .Append(" A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID INNER JOIN")
                .Append(" D_Case ON A_Invoices.Invoice_CaseID = D_Case.Case_ID INNER JOIN")
                .Append(" D_Company ON A_Invoices.Invoice_CompanyID = D_Company.Company_ID INNER JOIN")
                .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN")
                .Append(" D_Person D_Person_Candidate ON D_Case.Case_CandidateID = D_Person_Candidate.Person_ID ON")
                .Append(" D_Person_Contact.Person_ID = D_Case.Case_ContactID LEFT OUTER JOIN")
                .Append(" D_Address INNER JOIN")
                .Append(" D_AddressLink ON D_Address.Address_ID = D_AddressLink.AddressLink_AddressID ON")
                .Append(" D_Company.Company_ID = D_AddressLink.AddressLink_CompanyID")
                .Append(" WHERE ((D_AddressLink.AddressLink_Type IN (2, 3)) or addresslink_Id is null)")
                .Append(" AND A_Invoices.Invoice_ID = " & GlobalCRM.CurrentInvoiceId)
            End With




        Else
            With SQL
                .Append(" SELECT A_Invoices.Invoice_ID as Charge_InvoiceID, A_Invoices.Invoice_Date, A_Invoices.Invoice_DateDue as Invoice_DueDate, L_Products.Product_Name, D_Case.Case_ID, A_Charges.Charge_Type,")
                .Append(" A_Charges.Charge_Amount, A_Charges.Charge_VAT, D_Person.Person_FName + ' ' + D_Person.Person_LName AS Candidate")
                .Append(" , isnull(CASE address_text WHEN '' THEN '' ELSE address_text + char(13) + char(10) END + CASE address_region WHEN '' THEN '' ELSE address_region + ', ' END + CASE address_postcode WHEN '' THEN '' ELSE address_postcode + char(13) + char(10) END + CASE address_country WHEN '' THEN '' When 'UNITED KINGDOM' then '' ELSE address_country + char(13) + char(10) END,'') AS Address")
                .Append(" FROM         A_Invoices INNER JOIN")
                .Append(" A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID INNER JOIN")
                .Append(" D_Case ON A_Invoices.Invoice_CaseID = D_Case.Case_ID INNER JOIN")
                .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID INNER JOIN")
                .Append(" D_Person ON A_Invoices.Invoice_PersonID = D_Person.Person_ID LEFT OUTER JOIN")
                .Append(" D_Address INNER JOIN")
                .Append(" D_AddressLink ON D_Address.Address_ID = D_AddressLink.AddressLink_AddressID ON")
                .Append(" D_Person.Person_ID = D_AddressLink.AddressLink_PersonID")
                .Append(" WHERE     (D_AddressLink.AddressLink_Type IN (2, 3)) AND (A_Invoices.Invoice_ID = " & GlobalCRM.CurrentInvoiceId & ") OR")
                .Append(" (A_Invoices.Invoice_ID = " & GlobalCRM.CurrentInvoiceId & ") AND (D_AddressLink.AddressLink_ID IS NULL)")
            End With





        End If

        Return SQL.ToString

    End Function
    Private Shared Function SQL_PrivateReceipt(ByVal PaymentID As Long) As String
        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT A_Payments.Payment_ID, A_Payments.Payment_Amount, A_Payments.Payment_Method, A_Payments.Payment_EnteredBy, ")
            .Append(" A_Payments.Payment_DTEntered,D_Case.Case_ID, L_Products.Product_Name, D_Person.Person_Fname + ' ' + Person_LName as Candidate,")
            .Append(" CASE invaddress.address_text WHEN '' THEN '' ELSE invaddress.address_text + char(13) + char(10) END + CASE invaddress.address_region WHEN '' THEN '' ELSE invaddress.address_region + char(13) + char(10) END + CASE invaddress.address_postcode WHEN '' THEN '' ELSE invaddress.address_postcode + char(13) + char(10) END + CASE invaddress.address_country WHEN '' THEN '' ELSE invaddress.address_country END AS Address")
            .Append(" FROM A_Payments INNER JOIN")
            .Append(" D_Case ON A_Payments.Payment_CaseID = D_Case.Case_ID INNER JOIN")
            .Append(" D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID LEFT OUTER JOIN")
            .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN")
            .Append(" (SELECT     D_AddressLink.AddressLink_PersonID, D_Address.Address_Text, D_Address.Address_Postcode, D_Address.Address_Region,")
            .Append(" D_Address.Address_Country")
            .Append(" FROM D_AddressLink INNER JOIN")
            .Append(" D_Address ON D_AddressLink.AddressLink_AddressID = D_Address.Address_ID")
            .Append(" WHERE D_AddressLink.AddressLink_Type IN (2, 3)) InvAddress ON D_Person.Person_ID = InvAddress.AddressLink_PersonID")
            .Append(" WHERE (A_Payments.Payment_ID = " & PaymentID & ")")
        End With

        Return SQL.ToString
    End Function


    Private Shared Function SQL_CorporateReceipt(ByVal PaymentID As Long) As String
        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT A_Payments.Payment_ID, A_Payments.Payment_Amount, A_Payments.Payment_Method, A_Payments.Payment_EnteredBy, ")
            .Append(" A_Payments.Payment_DTEntered, D_Company.Company_Name,D_Case.Case_ID, L_Products.Product_Name, D_Person_Candidate.Person_Fname + ' ' + D_Person_Candidate.Person_LName as Candidate, D_Person_Contact.Person_Fname + ' ' + D_Person_Contact.Person_LName as Contact,")
            .Append(" CASE invaddress.address_text WHEN '' THEN '' ELSE invaddress.address_text + char(13) + char(10) END + CASE invaddress.address_region WHEN '' THEN '' ELSE invaddress.address_region + char(13) + char(10) END + CASE invaddress.address_postcode WHEN '' THEN '' ELSE invaddress.address_postcode + char(13) + char(10) END + CASE invaddress.address_country WHEN '' THEN '' ELSE invaddress.address_country END AS Address")
            .Append(" FROM A_Payments INNER JOIN")
            .Append(" D_Case ON A_Payments.Payment_CaseID = D_Case.Case_ID INNER JOIN")
            .Append(" D_Person D_Person_Candidate ON D_Case.Case_CandidateID = D_Person_Candidate.Person_ID LEFT OUTER JOIN")
            .Append(" D_Person D_Person_Contact ON D_Case.Case_ContactID = D_Person_Contact.Person_ID LEFT OUTER JOIN")
            .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN")
            .Append(" D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID LEFT OUTER JOIN")
            .Append(" (SELECT D_AddressLink.AddressLink_CompanyID, D_Address.Address_Text, D_Address.Address_Postcode, D_Address.Address_Region,")
            .Append(" D_Address.Address_Country")
            .Append(" FROM D_AddressLink INNER JOIN")
            .Append(" D_Address ON D_AddressLink.AddressLink_AddressID = D_Address.Address_ID")
            .Append(" WHERE D_AddressLink.AddressLink_Type IN (1, 3)) InvAddress ON D_Company.Company_ID = InvAddress.AddressLink_CompanyID")
            .Append(" WHERE (A_Payments.Payment_ID = " & PaymentID & ")")
        End With

        Return SQL.ToString
    End Function
    
End Class
